

March 10, 2016

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??


Trusted Members
Moderators
Power BI

January 31, 2022



Trusted Members
Moderators
Power BI

January 31, 2022

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)
