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??
Not sure what you are asking but perhaps the formulas in column M do what you need. If not, come back with some clarification.
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
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.
