My friend is struggling with using Excel to calculate hours for payroll, and is having difficulty. She posted this:
Does anyone have a clue to the solution?
Combine date and time into one cell, then do your hours operation. So the person worked from 1/1/2015 12:00AM to 1/2/2015 12:00AM. That works out to 1 day or 24 hours, as intended.
If you need to combine dates and times that are already in two different cells, you can just add them together and format the result to show as a date and time.
Time and Excel can be tricky and some operations are misleading. I’ve learned to distrust the TIME function when doing calculations.
I’m not sure what the exact application your friend is attempting but it might be useful to understand the following.
This statement
9/1/2015 12:00 AM+Time(24,0,0)
is misleading and results in 9/1/2015 12:00 AM
Instead of TIME I just use the hours divided by 24.
So the above calculation would be changed to
9/1/2015 12:00 AM+(24/24)
which would result properly in 9/2/2015 12:00 AM
Excel date OR time, is stored as number of days since 1900, any date no how its viewed. This is a decimal date, as in 0.25 means , if as an absolute, 6am because that is 1/4 of the way through the day, or for add or subtract, it means 6 hours. So when you enter a TIME, it actually stores it as the number of days since 1900… So thats why 00:00 is the same as 24:00, it really had no date so it translated for you… (it didn’t want to stuff up people who put 24:15 to start the day ! ). However thats when using a time format for entry, the : tells excel its a time…
Maybe why the military would work from 0 H00 to 23.00 H…
(Why doesn’t excel handle military time spec? Why doesn’t it store a simple time as a time ? )
You can use full date time, that works… just keep the view at full date so you don’t stuff up the day number (the integer part of the value stored.)
Perhaps you chose to use date separate to time, and so you want to a time ?
How about using decimal time, as in 00.15 for quarter past midnight, 23.50 for 11:30 pm…)
How about enter 24 for ending at midnight, and 0 for starting at midnight
Because its a simple decimal being entered, its stored as a number not a date.
Therefore it stores 24 or 23.5 or whatever.
You can then even have the formula work out that starting at 18 and finishing at 3 is not -15, just add 24 if its negative… 9 hours!