Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Non-working Dates and Times
=IF(OR(D41="",F41=""),0,(NETWORKDAYS(D41,F41)-1)*("15:00"-"06:30")+MOD(F41,1)-MOD(D41,1))
Im trying to calculate the non-working time accumulated by a process outside the normal shift of M-F 0630-1500 timeframe. But if all the work is done on a non-working day (Saturday or Sunday) I get the negative dates or time symbol ########, or in some cases hours after or before the time above for M-F. Ive checked the dates and theyre correct. Can someone explain possibly other reasons why this may be occurring? Mike |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Non-working Dates and Times
Give us an example of your data (the start and end date/times), and the
result you want to see. What happens if you start outside of working hours, but finish inside? You'll need to decide on what your definition of "working hours" is. Regards, Fred. "watermt" wrote in message ... =IF(OR(D41="",F41=""),0,(NETWORKDAYS(D41,F41)-1)*("15:00"-"06:30")+MOD(F41,1)-MOD(D41,1)) Im trying to calculate the non-working time accumulated by a process outside the normal shift of M-F 0630-1500 timeframe. But if all the work is done on a non-working day (Saturday or Sunday) I get the negative dates or time symbol ########, or in some cases hours after or before the time above for M-F. Ive checked the dates and theyre correct. Can someone explain possibly other reasons why this may be occurring? Mike |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Non-working Dates and Times
Would you like me to email you a sample file? If so, let me know how I can
get that to you? Mike "Fred Smith" wrote: Give us an example of your data (the start and end date/times), and the result you want to see. What happens if you start outside of working hours, but finish inside? You'll need to decide on what your definition of "working hours" is. Regards, Fred. "watermt" wrote in message ... =IF(OR(D41="",F41=""),0,(NETWORKDAYS(D41,F41)-1)*("15:00"-"06:30")+MOD(F41,1)-MOD(D41,1)) Im trying to calculate the non-working time accumulated by a process outside the normal shift of M-F 0630-1500 timeframe. But if all the work is done on a non-working day (Saturday or Sunday) I get the negative dates or time symbol ########, or in some cases hours after or before the time above for M-F. Ive checked the dates and theyre correct. Can someone explain possibly other reasons why this may be occurring? Mike |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Non-working Dates and Times
No. Most people don't like to open files because of the risk of viruses. If
you absolutely have to have someone look at the file, then upload it to a file hosting website (there are many), then post the link. But don't be surprised if people don't open the file. Just show a sample of what you need done. How difficult is it to give a few examples? Something like: Start Saturday at 07:00, end Saturday as 12:00 is 5 hours time worked. Start Saturday at 13:00, end Sunday at 15:00 is ? hours time worked. Start Sunday at 14:00, end Monday at 12:00 is ? hours time worked. Only you know what situations you need to handle, and what result you want. Regards, Fred. "watermt" wrote in message ... Would you like me to email you a sample file? If so, let me know how I can get that to you? Mike "Fred Smith" wrote: Give us an example of your data (the start and end date/times), and the result you want to see. What happens if you start outside of working hours, but finish inside? You'll need to decide on what your definition of "working hours" is. Regards, Fred. "watermt" wrote in message ... =IF(OR(D41="",F41=""),0,(NETWORKDAYS(D41,F41)-1)*("15:00"-"06:30")+MOD(F41,1)-MOD(D41,1)) Im trying to calculate the non-working time accumulated by a process outside the normal shift of M-F 0630-1500 timeframe. But if all the work is done on a non-working day (Saturday or Sunday) I get the negative dates or time symbol ########, or in some cases hours after or before the time above for M-F. Ive checked the dates and theyre correct. Can someone explain possibly other reasons why this may be occurring? Mike |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Non-working Dates and Times
No problem, here we go:
Start Monday 2/23/2009 18:10; End Tuesday 2/24/2009 06:30; 13 hours 20 minutes non-working weekday (M-F) time Start Thursday 2/12/2009 09:34; End Thursday 2/12/2009 15:29; 5 hours 55 minutes working weekday (M-F) time Start Wednesday 2/11/2009 23:39; End Thursday 2/12/2009 06:30; 6 hours 51 minutes working weekday (M-F) time Start Saturday 2/7/2009 15:21; End Monday 2/9/2009 06:30; 39 hours 9 minutes non-working weekend time Again, the department I'm working with has normal business hours M-F 06:30 to 15:30. Thanks again, Mike "Fred Smith" wrote: No. Most people don't like to open files because of the risk of viruses. If you absolutely have to have someone look at the file, then upload it to a file hosting website (there are many), then post the link. But don't be surprised if people don't open the file. Just show a sample of what you need done. How difficult is it to give a few examples? Something like: Start Saturday at 07:00, end Saturday as 12:00 is 5 hours time worked. Start Saturday at 13:00, end Sunday at 15:00 is ? hours time worked. Start Sunday at 14:00, end Monday at 12:00 is ? hours time worked. Only you know what situations you need to handle, and what result you want. Regards, Fred. "watermt" wrote in message ... Would you like me to email you a sample file? If so, let me know how I can get that to you? Mike "Fred Smith" wrote: Give us an example of your data (the start and end date/times), and the result you want to see. What happens if you start outside of working hours, but finish inside? You'll need to decide on what your definition of "working hours" is. Regards, Fred. "watermt" wrote in message ... =IF(OR(D41="",F41=""),0,(NETWORKDAYS(D41,F41)-1)*("15:00"-"06:30")+MOD(F41,1)-MOD(D41,1)) Im trying to calculate the non-working time accumulated by a process outside the normal shift of M-F 0630-1500 timeframe. But if all the work is done on a non-working day (Saturday or Sunday) I get the negative dates or time symbol ########, or in some cases hours after or before the time above for M-F. Ive checked the dates and theyre correct. Can someone explain possibly other reasons why this may be occurring? Mike |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Non-working Dates and Times
This is a lot more complicated than your original post. It seems to me
you're looking to calculate three different things: "working weekday time", "non-working weekday time" and "non-working weekend time". As many people won't be following this post, your best bet is to start a new thread. Be specific in what you need. From what I can see you need to specify: 1. How do you determine what "time zone" you're in? By the start time? end time? 2. What do you want the output to look like? If you simply show a time, as in your original formula, you won't know whether it's "working", "non-working", etc. 3. What happens if you start in one zone (eg 13:00 on Friday), and end in another (eg 09:00 Saturday)? If you cover all the bases in your request, you'll get a workable answer. Regards, Fred. "watermt" wrote in message ... No problem, here we go: Start Monday 2/23/2009 18:10; End Tuesday 2/24/2009 06:30; 13 hours 20 minutes non-working weekday (M-F) time Start Thursday 2/12/2009 09:34; End Thursday 2/12/2009 15:29; 5 hours 55 minutes working weekday (M-F) time Start Wednesday 2/11/2009 23:39; End Thursday 2/12/2009 06:30; 6 hours 51 minutes working weekday (M-F) time Start Saturday 2/7/2009 15:21; End Monday 2/9/2009 06:30; 39 hours 9 minutes non-working weekend time Again, the department I'm working with has normal business hours M-F 06:30 to 15:30. Thanks again, Mike "Fred Smith" wrote: No. Most people don't like to open files because of the risk of viruses. If you absolutely have to have someone look at the file, then upload it to a file hosting website (there are many), then post the link. But don't be surprised if people don't open the file. Just show a sample of what you need done. How difficult is it to give a few examples? Something like: Start Saturday at 07:00, end Saturday as 12:00 is 5 hours time worked. Start Saturday at 13:00, end Sunday at 15:00 is ? hours time worked. Start Sunday at 14:00, end Monday at 12:00 is ? hours time worked. Only you know what situations you need to handle, and what result you want. Regards, Fred. "watermt" wrote in message ... Would you like me to email you a sample file? If so, let me know how I can get that to you? Mike "Fred Smith" wrote: Give us an example of your data (the start and end date/times), and the result you want to see. What happens if you start outside of working hours, but finish inside? You'll need to decide on what your definition of "working hours" is. Regards, Fred. "watermt" wrote in message ... =IF(OR(D41="",F41=""),0,(NETWORKDAYS(D41,F41)-1)*("15:00"-"06:30")+MOD(F41,1)-MOD(D41,1)) Im trying to calculate the non-working time accumulated by a process outside the normal shift of M-F 0630-1500 timeframe. But if all the work is done on a non-working day (Saturday or Sunday) I get the negative dates or time symbol ########, or in some cases hours after or before the time above for M-F. Ive checked the dates and theyre correct. Can someone explain possibly other reasons why this may be occurring? Mike |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Non-working Dates and Times
Fred,
1. How do you determine what "time zone" you're in? By the start time? end time? Answer: We are only dealing with CST 2. What do you want the output to look like? If you simply show a time, as in your original formula, you won't know whether it's "working", "non-working", etc. Answer: Need to know if the event occurred before 0630 or after 1500 each day 3. What happens if you start in one zone (e.g. 13:00 on Friday), and end in Another (e.g. 09:00 Saturday)? Answer: Again, we're only dealing with one time zone - CST I simply need the total amount of time (hh:mm) a process was worked on when a department has no staff working (their hours are 0630 - 1500 M-F). The reat of the hospital works 24/7. I'm trying to account for work time performed by these other disciplines on a particular sub-process (steps) that must be completed to finish the overall process (i.e., dietary consultations). Example, a patient might be admitted and seen by a doctor on the weekend, these are sub-processes. The Dietary department doesn't start their work until Monday morning at 0630 and ends their shift at 1500 Monday). If you think I need to re-word my plea for assistance please let me know, I appreciate your time. I could also do a screen print of my Excel file and past that picture into an email for anyone willing to take a look! Mike "Fred Smith" wrote: This is a lot more complicated than your original post. It seems to me you're looking to calculate three different things: "working weekday time", "non-working weekday time" and "non-working weekend time". As many people won't be following this post, your best bet is to start a new thread. Be specific in what you need. From what I can see you need to specify: 1. How do you determine what "time zone" you're in? By the start time? end time? 2. What do you want the output to look like? If you simply show a time, as in your original formula, you won't know whether it's "working", "non-working", etc. 3. What happens if you start in one zone (eg 13:00 on Friday), and end in another (eg 09:00 Saturday)? If you cover all the bases in your request, you'll get a workable answer. Regards, Fred. "watermt" wrote in message ... No problem, here we go: Start Monday 2/23/2009 18:10; End Tuesday 2/24/2009 06:30; 13 hours 20 minutes non-working weekday (M-F) time Start Thursday 2/12/2009 09:34; End Thursday 2/12/2009 15:29; 5 hours 55 minutes working weekday (M-F) time Start Wednesday 2/11/2009 23:39; End Thursday 2/12/2009 06:30; 6 hours 51 minutes working weekday (M-F) time Start Saturday 2/7/2009 15:21; End Monday 2/9/2009 06:30; 39 hours 9 minutes non-working weekend time Again, the department I'm working with has normal business hours M-F 06:30 to 15:30. Thanks again, Mike "Fred Smith" wrote: No. Most people don't like to open files because of the risk of viruses. If you absolutely have to have someone look at the file, then upload it to a file hosting website (there are many), then post the link. But don't be surprised if people don't open the file. Just show a sample of what you need done. How difficult is it to give a few examples? Something like: Start Saturday at 07:00, end Saturday as 12:00 is 5 hours time worked. Start Saturday at 13:00, end Sunday at 15:00 is ? hours time worked. Start Sunday at 14:00, end Monday at 12:00 is ? hours time worked. Only you know what situations you need to handle, and what result you want. Regards, Fred. "watermt" wrote in message ... Would you like me to email you a sample file? If so, let me know how I can get that to you? Mike "Fred Smith" wrote: Give us an example of your data (the start and end date/times), and the result you want to see. What happens if you start outside of working hours, but finish inside? You'll need to decide on what your definition of "working hours" is. Regards, Fred. "watermt" wrote in message ... =IF(OR(D41="",F41=""),0,(NETWORKDAYS(D41,F41)-1)*("15:00"-"06:30")+MOD(F41,1)-MOD(D41,1)) Im trying to calculate the non-working time accumulated by a process outside the normal shift of M-F 0630-1500 timeframe. But if all the work is done on a non-working day (Saturday or Sunday) I get the negative dates or time symbol ########, or in some cases hours after or before the time above for M-F. Ive checked the dates and theyre correct. Can someone explain possibly other reasons why this may be occurring? Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calulate working hours and minutes between 2 dates and times | Excel Discussion (Misc queries) | |||
Calculating difference between times on 2 dates | Excel Worksheet Functions | |||
Calculating the difference between 2 dates and times | Excel Discussion (Misc queries) | |||
Calculating Dates not always working | Excel Worksheet Functions | |||
NEED HELP - Calculating dates and times | Excel Discussion (Misc queries) |