Converting hours to days
babik
Posted messages
2
Status
Member
-
babik Posted messages 2 Status Member -
babik Posted messages 2 Status Member -
Hello
I am submitting my issue as follows:
in Excel 2010
A1---> number of hours in the format [hh]:mm:ss (hours worked)
B1---> 07:00 in the same format (workday)
C1---> =FLOOR(A1/B1;0)+A1-B1*FLOOR(A1/B1;0) in the format j"j"hh:mm
If:
A1= 220:00
B1= 7:00
C1= 31d 03:00 so CORRECT (workdays of 7:00)
problem n° 1
A1=227:00
B1=7:00
C1= 1d03:00 instead of 32d03:00
so how to obtain days greater than 31 that apparently corresponds to a month obscured by the formula? Knowing that I have to remain in days and not show months that would be unmanageable (30d;31d;28d)
problem n° 2
to work with negative hours I use 1904 as the calendar reference
to offset the 1-day shift that I have noticed, I modified the formula as follows:
A1=220:00 in the format [hh]"h"mm;[Red]"-"[hh]"h"mm;0
B1=7:00 in the format [hh]:mm:ss
B2=24:00 in the format [hh]:mm:ss (Excel day)
C1---> =FLOOR(A1/B1;0)+A1-B1*FLOOR(A1/B1;0)-B2 so 31d03:00
A4=220:00 , A5=0:00 and A1=A4-A5=220h00
A4=220:00 , A5=440:00 and A1=A4-A5=-220h00
so if A1>0 I need to subtract B2 from the formula
if A1<0 I need to add B2 to the formula
How can we use either one or the other in C1 depending on the sign of A1?
I hope I am clear enough
I have been struggling with this formula for a week in vain
Thanks in advance
I am submitting my issue as follows:
in Excel 2010
A1---> number of hours in the format [hh]:mm:ss (hours worked)
B1---> 07:00 in the same format (workday)
C1---> =FLOOR(A1/B1;0)+A1-B1*FLOOR(A1/B1;0) in the format j"j"hh:mm
If:
A1= 220:00
B1= 7:00
C1= 31d 03:00 so CORRECT (workdays of 7:00)
problem n° 1
A1=227:00
B1=7:00
C1= 1d03:00 instead of 32d03:00
so how to obtain days greater than 31 that apparently corresponds to a month obscured by the formula? Knowing that I have to remain in days and not show months that would be unmanageable (30d;31d;28d)
problem n° 2
to work with negative hours I use 1904 as the calendar reference
to offset the 1-day shift that I have noticed, I modified the formula as follows:
A1=220:00 in the format [hh]"h"mm;[Red]"-"[hh]"h"mm;0
B1=7:00 in the format [hh]:mm:ss
B2=24:00 in the format [hh]:mm:ss (Excel day)
C1---> =FLOOR(A1/B1;0)+A1-B1*FLOOR(A1/B1;0)-B2 so 31d03:00
A4=220:00 , A5=0:00 and A1=A4-A5=220h00
A4=220:00 , A5=440:00 and A1=A4-A5=-220h00
so if A1>0 I need to subtract B2 from the formula
if A1<0 I need to add B2 to the formula
How can we use either one or the other in C1 depending on the sign of A1?
I hope I am clear enough
I have been struggling with this formula for a week in vain
Thanks in advance
4 answers
problem no. 1.
I found a similar formula
=ROUND(A1/B1;0)"j "&ROUND(MOD(A1;B1)*24;0)"h"
which is valid for durations with whole hours (no minutes).
Thus with 227:00:00 in A1, it will display 32j 3h.
If the durations include minutes, it will be
=ROUND(A1/B1;0)"j "&ROUND(MOD(A1;B1)*24;2)"h"
Thus with 227:15:00 in A1, it will display 32j 3.25h.
babik must note that our 3 formulas give a result in TEXT format, thus unusable for other calculations!
Best regards.
--
Retirement is nice! Especially in the Antilles... :-)
Raymond (INSA, AFPA, CF/R)
I found a similar formula
=ROUND(A1/B1;0)"j "&ROUND(MOD(A1;B1)*24;0)"h"
which is valid for durations with whole hours (no minutes).
Thus with 227:00:00 in A1, it will display 32j 3h.
If the durations include minutes, it will be
=ROUND(A1/B1;0)"j "&ROUND(MOD(A1;B1)*24;2)"h"
Thus with 227:15:00 in A1, it will display 32j 3.25h.
babik must note that our 3 formulas give a result in TEXT format, thus unusable for other calculations!
Best regards.
--
Retirement is nice! Especially in the Antilles... :-)
Raymond (INSA, AFPA, CF/R)
problem no. 2.
Don't change the calendar.
Don't remove a day.
You need two different columns to calculate the durations.
Move B1 and B2 to column C
In A1, put the formula =IF(A4>C4,A4-C4,0)
In B1, put the formula =IF(A4<c4 />thus column A totals the extra hours and column B the hours deducted; in the last row, it will simply be Total A - Total B
--
It's nice, retirement! Especially in the Caribbean... :-)
Raymond (INSA, AFPA, CF/R)
Don't change the calendar.
Don't remove a day.
You need two different columns to calculate the durations.
Move B1 and B2 to column C
In A1, put the formula =IF(A4>C4,A4-C4,0)
In B1, put the formula =IF(A4<c4 />thus column A totals the extra hours and column B the hours deducted; in the last row, it will simply be Total A - Total B
--
It's nice, retirement! Especially in the Caribbean... :-)
Raymond (INSA, AFPA, CF/R)
If the display of 00 min doesn't put you off
=INT(A1/B1)"j "&SUBSTITUTE(SUBSTITUTE(TEXT((A1-INT(A1/B1)*B1);"hh:mm:ss");":";" h ";1);":";" min ";1)" s"