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

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

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