| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
calulate working hours and minutes between 2 dates and times
I need to calculate the working hours and minutes between two dates and times.
saturday and sunday need to be excluded and the core hours are 08:00 to 17:00. I have a task that needs to be completed in 2 working hours but can arrive any time in the 24 hour clock including weekends but the task time starts from the working hours. At 17:00 the clock stops and begins again at 08:00 next working day. date time format is m:d:yyyy h:mm Task are presented across the rows so for example arrival time column M and task close time column O. There is a column for overall time but I just need the working hours and minutes taken to complete. A complete week list is about 6000 rows so you can see the pain to do this manually. I have scoured the internet / this site for an answer but at present have had no luck getting something to work. Can anbody help please -- nigeo |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
calulate working hours and minutes between 2 dates and times
Try this,
=((NETWORKDAYS(A1,B1)-1)*(J$1-I$1)+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1),J$1,I$ 1),J$1)-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),J$1,I$1))*24 Whe- A1 = start date/time formatted mm/dd/yyyy hh:mm B1 = end date/time formatted mm/dd/yyyy hh:mm I1 = Workday start formatted hh:mm J1 = Workday end formatted hh:mm the formula is draggable for other date/times in columns A & B Mike "nigeo" wrote: I need to calculate the working hours and minutes between two dates and times. saturday and sunday need to be excluded and the core hours are 08:00 to 17:00. I have a task that needs to be completed in 2 working hours but can arrive any time in the 24 hour clock including weekends but the task time starts from the working hours. At 17:00 the clock stops and begins again at 08:00 next working day. date time format is m:d:yyyy h:mm Task are presented across the rows so for example arrival time column M and task close time column O. There is a column for overall time but I just need the working hours and minutes taken to complete. A complete week list is about 6000 rows so you can see the pain to do this manually. I have scoured the internet / this site for an answer but at present have had no luck getting something to work. Can anbody help please -- nigeo |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
calulate working hours and minutes between 2 dates and times
A bit simpler
=((NETWORKDAYS(A1,B1)-1)*("17:00"-"08:00")+MOD(B1,1)-MOD(A1,1))*24 And if you want you can add the additional argument where Holidays is a named range that contains holiday dates. Format both as general =((NETWORKDAYS(A1,B1,holidays)-1)*("17:00"-"08:00")+MOD(B1,1)-MOD(A1,1))*24 Mike "Mike H" wrote: Try this, =((NETWORKDAYS(A1,B1)-1)*(J$1-I$1)+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1),J$1,I$ 1),J$1)-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),J$1,I$1))*24 Whe- A1 = start date/time formatted mm/dd/yyyy hh:mm B1 = end date/time formatted mm/dd/yyyy hh:mm I1 = Workday start formatted hh:mm J1 = Workday end formatted hh:mm the formula is draggable for other date/times in columns A & B Mike "nigeo" wrote: I need to calculate the working hours and minutes between two dates and times. saturday and sunday need to be excluded and the core hours are 08:00 to 17:00. I have a task that needs to be completed in 2 working hours but can arrive any time in the 24 hour clock including weekends but the task time starts from the working hours. At 17:00 the clock stops and begins again at 08:00 next working day. date time format is m:d:yyyy h:mm Task are presented across the rows so for example arrival time column M and task close time column O. There is a column for overall time but I just need the working hours and minutes taken to complete. A complete week list is about 6000 rows so you can see the pain to do this manually. I have scoured the internet / this site for an answer but at present have had no luck getting something to work. Can anbody help please -- nigeo |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
calulate working hours and minutes between 2 dates and times
Hi
This site might help. Kaveh http://www.cpearson.com/excel/datetime.htm "nigeo" wrote: I need to calculate the working hours and minutes between two dates and times. saturday and sunday need to be excluded and the core hours are 08:00 to 17:00. I have a task that needs to be completed in 2 working hours but can arrive any time in the 24 hour clock including weekends but the task time starts from the working hours. At 17:00 the clock stops and begins again at 08:00 next working day. date time format is m:d:yyyy h:mm Task are presented across the rows so for example arrival time column M and task close time column O. There is a column for overall time but I just need the working hours and minutes taken to complete. A complete week list is about 6000 rows so you can see the pain to do this manually. I have scoured the internet / this site for an answer but at present have had no luck getting something to work. Can anbody help please -- nigeo |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
calulate working hours and minutes between 2 dates and times
cheers mike nearly there what format do the results cell have to show hours
and minutes -- nigeo "Mike H" wrote: Try this, =((NETWORKDAYS(A1,B1)-1)*(J$1-I$1)+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1),J$1,I$ 1),J$1)-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),J$1,I$1))*24 Whe- A1 = start date/time formatted mm/dd/yyyy hh:mm B1 = end date/time formatted mm/dd/yyyy hh:mm I1 = Workday start formatted hh:mm J1 = Workday end formatted hh:mm the formula is draggable for other date/times in columns A & B Mike "nigeo" wrote: I need to calculate the working hours and minutes between two dates and times. saturday and sunday need to be excluded and the core hours are 08:00 to 17:00. I have a task that needs to be completed in 2 working hours but can arrive any time in the 24 hour clock including weekends but the task time starts from the working hours. At 17:00 the clock stops and begins again at 08:00 next working day. date time format is m:d:yyyy h:mm Task are presented across the rows so for example arrival time column M and task close time column O. There is a column for overall time but I just need the working hours and minutes taken to complete. A complete week list is about 6000 rows so you can see the pain to do this manually. I have scoured the internet / this site for an answer but at present have had no luck getting something to work. Can anbody help please -- nigeo |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
calulate working hours and minutes between 2 dates and times
hi,
Both will work in hours minutes if you delete the *24 at the and and format as HH:MM Mike "nigeo" wrote: cheers mike nearly there what format do the results cell have to show hours and minutes -- nigeo "Mike H" wrote: Try this, =((NETWORKDAYS(A1,B1)-1)*(J$1-I$1)+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1),J$1,I$ 1),J$1)-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),J$1,I$1))*24 Whe- A1 = start date/time formatted mm/dd/yyyy hh:mm B1 = end date/time formatted mm/dd/yyyy hh:mm I1 = Workday start formatted hh:mm J1 = Workday end formatted hh:mm the formula is draggable for other date/times in columns A & B Mike "nigeo" wrote: I need to calculate the working hours and minutes between two dates and times. saturday and sunday need to be excluded and the core hours are 08:00 to 17:00. I have a task that needs to be completed in 2 working hours but can arrive any time in the 24 hour clock including weekends but the task time starts from the working hours. At 17:00 the clock stops and begins again at 08:00 next working day. date time format is m:d:yyyy h:mm Task are presented across the rows so for example arrival time column M and task close time column O. There is a column for overall time but I just need the working hours and minutes taken to complete. A complete week list is about 6000 rows so you can see the pain to do this manually. I have scoured the internet / this site for an answer but at present have had no luck getting something to work. Can anbody help please -- nigeo |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
calulate working hours and minutes between 2 dates and times
the simpler version does't appear to work it gives a result out of working
hours (not required) the original version appears to be ok but appears to give the result as decimal, what can i format that to, to get hh:mm nigeo "Mike H" wrote: A bit simpler =((NETWORKDAYS(A1,B1)-1)*("17:00"-"08:00")+MOD(B1,1)-MOD(A1,1))*24 And if you want you can add the additional argument where Holidays is a named range that contains holiday dates. Format both as general =((NETWORKDAYS(A1,B1,holidays)-1)*("17:00"-"08:00")+MOD(B1,1)-MOD(A1,1))*24 Mike "Mike H" wrote: Try this, =((NETWORKDAYS(A1,B1)-1)*(J$1-I$1)+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1),J$1,I$ 1),J$1)-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),J$1,I$1))*24 Whe- A1 = start date/time formatted mm/dd/yyyy hh:mm B1 = end date/time formatted mm/dd/yyyy hh:mm I1 = Workday start formatted hh:mm J1 = Workday end formatted hh:mm the formula is draggable for other date/times in columns A & B Mike "nigeo" wrote: I need to calculate the working hours and minutes between two dates and times. saturday and sunday need to be excluded and the core hours are 08:00 to 17:00. I have a task that needs to be completed in 2 working hours but can arrive any time in the 24 hour clock including weekends but the task time starts from the working hours. At 17:00 the clock stops and begins again at 08:00 next working day. date time format is m:d:yyyy h:mm Task are presented across the rows so for example arrival time column M and task close time column O. There is a column for overall time but I just need the working hours and minutes taken to complete. A complete week list is about 6000 rows so you can see the pain to do this manually. I have scoured the internet / this site for an answer but at present have had no luck getting something to work. Can anbody help please -- nigeo |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
calulate working hours and minutes between 2 dates and times
Kaveh thanks, tried that but couldn't get it to work, mikes first response
appears to work just need the results in hh:mm -- nigeo "kaveh" wrote: Hi This site might help. Kaveh http://www.cpearson.com/excel/datetime.htm "nigeo" wrote: I need to calculate the working hours and minutes between two dates and times. saturday and sunday need to be excluded and the core hours are 08:00 to 17:00. I have a task that needs to be completed in 2 working hours but can arrive any time in the 24 hour clock including weekends but the task time starts from the working hours. At 17:00 the clock stops and begins again at 08:00 next working day. date time format is m:d:yyyy h:mm Task are presented across the rows so for example arrival time column M and task close time column O. There is a column for overall time but I just need the working hours and minutes taken to complete. A complete week list is about 6000 rows so you can see the pain to do this manually. I have scoured the internet / this site for an answer but at present have had no luck getting something to work. Can anbody help please -- nigeo |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
calulate working hours and minutes between 2 dates and times
Remove the *24 and format as [hh]:mm
-- Regards, Peo Sjoblom "nigeo" wrote in message ... the simpler version does't appear to work it gives a result out of working hours (not required) the original version appears to be ok but appears to give the result as decimal, what can i format that to, to get hh:mm nigeo "Mike H" wrote: A bit simpler =((NETWORKDAYS(A1,B1)-1)*("17:00"-"08:00")+MOD(B1,1)-MOD(A1,1))*24 And if you want you can add the additional argument where Holidays is a named range that contains holiday dates. Format both as general =((NETWORKDAYS(A1,B1,holidays)-1)*("17:00"-"08:00")+MOD(B1,1)-MOD(A1,1))*24 Mike "Mike H" wrote: Try this, =((NETWORKDAYS(A1,B1)-1)*(J$1-I$1)+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1),J$1,I$ 1),J$1)-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),J$1,I$1))*24 Whe- A1 = start date/time formatted mm/dd/yyyy hh:mm B1 = end date/time formatted mm/dd/yyyy hh:mm I1 = Workday start formatted hh:mm J1 = Workday end formatted hh:mm the formula is draggable for other date/times in columns A & B Mike "nigeo" wrote: I need to calculate the working hours and minutes between two dates and times. saturday and sunday need to be excluded and the core hours are 08:00 to 17:00. I have a task that needs to be completed in 2 working hours but can arrive any time in the 24 hour clock including weekends but the task time starts from the working hours. At 17:00 the clock stops and begins again at 08:00 next working day. date time format is m:d:yyyy h:mm Task are presented across the rows so for example arrival time column M and task close time column O. There is a column for overall time but I just need the working hours and minutes taken to complete. A complete week list is about 6000 rows so you can see the pain to do this manually. I have scoured the internet / this site for an answer but at present have had no luck getting something to work. Can anbody help please -- nigeo |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
calulate working hours and minutes between 2 dates and times
the original answer from mike works with the last bit from Peo Sjoblom
thanks to all who offered advice, will be back with new problem soon. thanks again -- nigeo "Peo Sjoblom" wrote: Remove the *24 and format as [hh]:mm -- Regards, Peo Sjoblom "nigeo" wrote in message ... the simpler version does't appear to work it gives a result out of working hours (not required) the original version appears to be ok but appears to give the result as decimal, what can i format that to, to get hh:mm nigeo "Mike H" wrote: A bit simpler =((NETWORKDAYS(A1,B1)-1)*("17:00"-"08:00")+MOD(B1,1)-MOD(A1,1))*24 And if you want you can add the additional argument where Holidays is a named range that contains holiday dates. Format both as general =((NETWORKDAYS(A1,B1,holidays)-1)*("17:00"-"08:00")+MOD(B1,1)-MOD(A1,1))*24 Mike "Mike H" wrote: Try this, =((NETWORKDAYS(A1,B1)-1)*(J$1-I$1)+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1),J$1,I$ 1),J$1)-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),J$1,I$1))*24 Whe- A1 = start date/time formatted mm/dd/yyyy hh:mm B1 = end date/time formatted mm/dd/yyyy hh:mm I1 = Workday start formatted hh:mm J1 = Workday end formatted hh:mm the formula is draggable for other date/times in columns A & B Mike "nigeo" wrote: I need to calculate the working hours and minutes between two dates and times. saturday and sunday need to be excluded and the core hours are 08:00 to 17:00. I have a task that needs to be completed in 2 working hours but can arrive any time in the 24 hour clock including weekends but the task time starts from the working hours. At 17:00 the clock stops and begins again at 08:00 next working day. date time format is m:d:yyyy h:mm Task are presented across the rows so for example arrival time column M and task close time column O. There is a column for overall time but I just need the working hours and minutes taken to complete. A complete week list is about 6000 rows so you can see the pain to do this manually. I have scoured the internet / this site for an answer but at present have had no luck getting something to work. Can anbody help please -- nigeo |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
calulate working hours and minutes between 2 dates and times
can I add something to this which seems to work perfectly, to accomodate
holidays -- nigeo "Mike H" wrote: Try this, =((NETWORKDAYS(A1,B1)-1)*(J$1-I$1)+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1),J$1,I$ 1),J$1)-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),J$1,I$1))*24 Whe- A1 = start date/time formatted mm/dd/yyyy hh:mm B1 = end date/time formatted mm/dd/yyyy hh:mm I1 = Workday start formatted hh:mm J1 = Workday end formatted hh:mm the formula is draggable for other date/times in columns A & B Mike "nigeo" wrote: I need to calculate the working hours and minutes between two dates and times. saturday and sunday need to be excluded and the core hours are 08:00 to 17:00. I have a task that needs to be completed in 2 working hours but can arrive any time in the 24 hour clock including weekends but the task time starts from the working hours. At 17:00 the clock stops and begins again at 08:00 next working day. date time format is m:d:yyyy h:mm Task are presented across the rows so for example arrival time column M and task close time column O. There is a column for overall time but I just need the working hours and minutes taken to complete. A complete week list is about 6000 rows so you can see the pain to do this manually. I have scoured the internet / this site for an answer but at present have had no luck getting something to work. Can anbody help please -- nigeo |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I calculate total pay from pay rate times hours and minutes | Excel Discussion (Misc queries) | |||
| Calculate hours and minutes between times when it goes past midnig | Excel Worksheet Functions | |||
| Adding hours/minutes to Date/Times | Excel Worksheet Functions | |||
| how to calulate time in hours cbetween two different dates? | Excel Worksheet Functions | |||
| How do I convert times from hours to minutes? | Excel Discussion (Misc queries) | |||
Linear Mode
