Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with Date Manipulation
Okay.. Im not a kid, 36, been working with excel for years & last year did up to intermediate courses in Access. However, that being said because you guys are very keen in here, so I dont want to sound stupid, whilst my understanding on alot of excel is generally on the layman terms! Heres my problem. Im making a simple spreadsheet. I have a widget that enters my facility on one date, it then exits another. I know by setting up EXIT - ENTER = DAYS I have my duration in the facility. BUT--- I dont want the weekends to count as days! Material arrives every weekday & exits the same. Some material flows every day.. some rarely with EVERYTHING in between. Any suggestions/solutions how to have this accomplished where the weekends are factored OUT of the equasion? Hope that made sence!!! I do appreciate your help! -- Sbufkle ------------------------------------------------------------------------ Sbufkle's Profile: http://www.excelforum.com/member.php...o&userid=29069 View this thread: http://www.excelforum.com/showthread...hreadid=487950 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with Date Manipulation
Hi
take a look at the NETWORKDAYS function. =NETWORKDAYS(enter_date,exit_date,holidays) Holidays is an optional argument, and is either a named range or a contiguous list of cell locations holding the public holiday dates. Networkdays is part of the Analysis Toolpak so you need to have this installed. ToolsAddinsselect Analysis ToolpakOK Regards Roger Govier Sbufkle wrote: Okay.. Im not a kid, 36, been working with excel for years & last year did up to intermediate courses in Access. However, that being said because you guys are very keen in here, so I dont want to sound stupid, whilst my understanding on alot of excel is generally on the layman terms! Heres my problem. Im making a simple spreadsheet. I have a widget that enters my facility on one date, it then exits another. I know by setting up EXIT - ENTER = DAYS I have my duration in the facility. BUT--- I dont want the weekends to count as days! Material arrives every weekday & exits the same. Some material flows every day.. some rarely with EVERYTHING in between. Any suggestions/solutions how to have this accomplished where the weekends are factored OUT of the equasion? Hope that made sence!!! I do appreciate your help! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with Date Manipulation
Have you looked at the NETWORKDAYS function?
Per Excel Help: If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in Does that help? *********** Regards, Ron "Sbufkle" wrote: Okay.. Im not a kid, 36, been working with excel for years & last year did up to intermediate courses in Access. However, that being said because you guys are very keen in here, so I dont want to sound stupid, whilst my understanding on alot of excel is generally on the layman terms! Heres my problem. Im making a simple spreadsheet. I have a widget that enters my facility on one date, it then exits another. I know by setting up EXIT - ENTER = DAYS I have my duration in the facility. BUT--- I dont want the weekends to count as days! Material arrives every weekday & exits the same. Some material flows every day.. some rarely with EVERYTHING in between. Any suggestions/solutions how to have this accomplished where the weekends are factored OUT of the equasion? Hope that made sence!!! I do appreciate your help! -- Sbufkle ------------------------------------------------------------------------ Sbufkle's Profile: http://www.excelforum.com/member.php...o&userid=29069 View this thread: http://www.excelforum.com/showthread...hreadid=487950 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with Date Manipulation
See the NETWORKDAYS() function.
It is an Analysis Toolpak function. If you get a #NAME error: ToolsAdd-ins, check Analysis Toolpak -- Kind regards, Niek Otten "Sbufkle" wrote in message ... Okay.. Im not a kid, 36, been working with excel for years & last year did up to intermediate courses in Access. However, that being said because you guys are very keen in here, so I dont want to sound stupid, whilst my understanding on alot of excel is generally on the layman terms! Heres my problem. Im making a simple spreadsheet. I have a widget that enters my facility on one date, it then exits another. I know by setting up EXIT - ENTER = DAYS I have my duration in the facility. BUT--- I dont want the weekends to count as days! Material arrives every weekday & exits the same. Some material flows every day.. some rarely with EVERYTHING in between. Any suggestions/solutions how to have this accomplished where the weekends are factored OUT of the equasion? Hope that made sence!!! I do appreciate your help! -- Sbufkle ------------------------------------------------------------------------ Sbufkle's Profile: http://www.excelforum.com/member.php...o&userid=29069 View this thread: http://www.excelforum.com/showthread...hreadid=487950 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with Date Manipulation
This is the first neat little trick I ever learned with excel.
What you need to do is set up a list of nonwroking days..... (don't forget to include bank holidays!) I had to do this manually which is a bit time consuming but you only need to it once, this needs to be on a separate worksheet. Then you need to go to your tools menu and check out 'Add Ins' you need the Analysis tool pak, just check the button and excel should do the rest. Then the function you need to use is networkdays something like: =NETWORKDAYS(start date,end date,nonworking days list) hope this helps "Sbufkle" wrote: Okay.. Im not a kid, 36, been working with excel for years & last year did up to intermediate courses in Access. However, that being said because you guys are very keen in here, so I dont want to sound stupid, whilst my understanding on alot of excel is generally on the layman terms! Heres my problem. Im making a simple spreadsheet. I have a widget that enters my facility on one date, it then exits another. I know by setting up EXIT - ENTER = DAYS I have my duration in the facility. BUT--- I dont want the weekends to count as days! Material arrives every weekday & exits the same. Some material flows every day.. some rarely with EVERYTHING in between. Any suggestions/solutions how to have this accomplished where the weekends are factored OUT of the equasion? Hope that made sence!!! I do appreciate your help! -- Sbufkle ------------------------------------------------------------------------ Sbufkle's Profile: http://www.excelforum.com/member.php...o&userid=29069 View this thread: http://www.excelforum.com/showthread...hreadid=487950 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Formatting Problem | Excel Discussion (Misc queries) | |||
Date sorting problem | Excel Discussion (Misc queries) | |||
Time Date Formula Problem | Excel Worksheet Functions | |||
Login Logout Date Problem | Excel Worksheet Functions | |||
Date and Time Picker Control problem | Excel Discussion (Misc queries) |