Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
converting working hours to days
I have 2 dates / times in colums C and L formatted mm/dd/yyyy hh:mm:ss
In column P I am using the formula =((NETWORKDAYS(C2,L2,$AF$2:$AF$9)-1)*(AD$2-AC$2)+IF(NETWORKDAYS(L2,L2),MEDIAN(MOD(L2,1),AD$2, AC$2),AD$2)-MEDIAN(NETWORKDAYS(C2,C2)*MOD(C2,1),AD$2,AC$2)) to work out working hours between the two date/times. this gives me hours and mins ok. in column V I need to convert this to days (working day =10 hours), i am using the formula =DAY((P2*2.4)+1), this works ok upto 309 hours which returns 31 days, after that it starts at 1 again, how can i get it to keep counting the days past 31 Regards nigeo |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
converting working hours to days
Instead of
=DAY((P2*2.4)+1), try =(P2*2.4)+1 -- Kind regards, Niek Otten Microsoft MVP - Excel "nigeo" wrote in message ... I have 2 dates / times in colums C and L formatted mm/dd/yyyy hh:mm:ss In column P I am using the formula =((NETWORKDAYS(C2,L2,$AF$2:$AF$9)-1)*(AD$2-AC$2)+IF(NETWORKDAYS(L2,L2),MEDIAN(MOD(L2,1),AD$2, AC$2),AD$2)-MEDIAN(NETWORKDAYS(C2,C2)*MOD(C2,1),AD$2,AC$2)) to work out working hours between the two date/times. this gives me hours and mins ok. in column V I need to convert this to days (working day =10 hours), i am using the formula =DAY((P2*2.4)+1), this works ok upto 309 hours which returns 31 days, after that it starts at 1 again, how can i get it to keep counting the days past 31 Regards nigeo |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
converting working hours to days
Thanks Niek just added INT to round it down, work great.
Regards -- nigeo "Niek Otten" wrote: Instead of =DAY((P2*2.4)+1), try =(P2*2.4)+1 -- Kind regards, Niek Otten Microsoft MVP - Excel "nigeo" wrote in message ... I have 2 dates / times in colums C and L formatted mm/dd/yyyy hh:mm:ss In column P I am using the formula =((NETWORKDAYS(C2,L2,$AF$2:$AF$9)-1)*(AD$2-AC$2)+IF(NETWORKDAYS(L2,L2),MEDIAN(MOD(L2,1),AD$2, AC$2),AD$2)-MEDIAN(NETWORKDAYS(C2,C2)*MOD(C2,1),AD$2,AC$2)) to work out working hours between the two date/times. this gives me hours and mins ok. in column V I need to convert this to days (working day =10 hours), i am using the formula =DAY((P2*2.4)+1), this works ok upto 309 hours which returns 31 days, after that it starts at 1 again, how can i get it to keep counting the days past 31 Regards nigeo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating Working Hours, Days and Overtimes. | Excel Discussion (Misc queries) | |||
Converting total number of hours (24 hours) into days | Excel Discussion (Misc queries) | |||
I need an Excel formula for converting hours to days? | Excel Worksheet Functions | |||
Problem converting Hours to Days, Hours, Minutes | Excel Worksheet Functions | |||
converting hours to days,hours,minutes | Excel Worksheet Functions |