Log In
Register
Home
March 10, 2016
Offline
In the file here I have defined the calculation of hours according to the entry and exit of an employee.
I received the number of hours in hour format,
when I convert it to a decimal number I get a problem.
I recognized that the time cell is set like this - 01/01/1900 06:08:00
and that's why it happens. What is the solution??
See Answer
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
Not sure what you are asking but perhaps the formulas in column M do what you need. If not, come back with some clarification.
March 10, 2016
Offline
Attached is the file with an explanation of the problem - in the blue cell F4 -
the answer should be 6.13 and here in the file you get the answer - 30.13 -
How can this be fixed?? Thanks
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
I believe you are struggling with calculating durations where end times may go beyond midnight. Since you are adding 1 to certain values in the formula. Even when the end time is on the same day as the start time.
That's why I used the MOD function. In column M in the attached file. The cells are now formatted as 'General' and it seems to work as you want.
Reverting to your initial question:
"I recognized that the time cell is set like this - 01/01/1900 06:08:00
and that's why it happens. What is the solution??"
When you add 1 to a time you are adding a day (24 hours). For Excel, a time alone like 06:08 is actually 0.255555555555556 (i.e. just over one quarter of a 24 hour day). Add 1 and you get 1.255555.... Excel interprets this as a Date/Time and displays 01/01/1900 06:08:00 in the formula bar, because day number 1 = January 1, 1900 in Excel's calendar.
PS: If durations may go beyond 24 hours you must use a custom format like [hh]:mm.
Answers Post
1 Guest(s)
