23:59 VS 00:00 as start/end time

Question

What I have:

I have a spread-sheet that I'm using as a time logger for every hour/minute of the day.

If I spent 8pm to 2am using StackOverflow, I'd log it as follows by splitting the task into two separate records at the juncture the day (or date) changes (splitting is a lazy option to make my formulae less verbose).

Date       | Start Time | End Time
----------------------------------
19/04/2016 | 20:00:00   | 23:59:00
20/04/2016 | 00:00:00   | 02:00:00

The problem:

When I run weekly reports, the sum of all hours/minutes per week is 167:53:00 so I'm obviously not accounting a minute per day of each week.

My question:

In the context of a spread-sheet...

1. What is the first minute of the day? 00:00:00 / 00:01:00?
2. What is the last minute of the day? 23:59:00 / 00:00:00?
3. Is the first and last minute of the day 00:00:00?

Answer 1

You need to distinguish between duration and time stamp. 0:00:00 is not a minute, it's a time stamp. A minute is a measure for duration. In the context of days, 0:00:00 is the time stamp at which the day starts. A minute as a measure of duration starts at one time stamp and extend to another time stamp that is sixty seconds later, e.g. starts at 0:00:00 and ends at 0:01:00. In between are sixty elapsed seconds. The first second has elapsed when the clock turns over to 0:00:01. The sixtieth second - or the first full minute - has elapsed when the clock turns from 0:00:59 to 0:01:00.

So, 0:00:00 marks the start of the day, 23:59:59 marks the start of the last second of the last minute of that day and once the clock turns over to next day 0:00:00, the first day is complete. Again, notice the difference between time stamp and duration.

If you subtract two timestamps, you get the duration, i.e. the time that elapsed between the two.

In your example above, the start time stamp is 8 pm and the end time stamp is one minute before midnight, not midnight. The elapsed time between 20:00:00 and 23:59:00 is not 4 hours, but 3:59:00 hrs, since the last minute to complete four hours has not been recorded.

As a time stamp that has no duration, 0:00:00 marks the end of the previous day and the beginning of the new day. To quote (allegedly) Leonardo da Vinci:

“In rivers, the water that you touch is the last of what has passed and the first of that which comes; so with present time.”

Edit: to address the question what must be entered into the above sample to correctly present the time:

0:00:00 is the time stamp at which today starts. Add one day to that to get the time stamp when tomorrow starts. Excel stores time values as decimals and days as whole numbers. To add one day to a time value, add the integer value 1, so the correct value to be entered is 1, which can be formatted to show as a time value only and will again be displayed as 0:00:00.

In order to calculate the difference, you need to add the day and the time values for start and end, then do the subtraction. In the following screenshot, C2 has the value 1, formatted with custom format h:mm:ss. The formula to calculate the elapsed time in cell D2 is =(A2+C2)-(A2+B2).

Your technique of splitting up the date and the time values is not very efficient if you plan to use these values for calculations. If your goal is to make formulas less verbose, enter each value as a date and time value in the same cell, then do a simple subtraction. In that case, start and end would be as per this screenshot and the formula is =B2-A2 formatted as h:mm:ss