Converting start-of-day dates to end-of-day in Apple Numbers

Question

Level 1

126 points

Converting start-of-day dates to end-of-day in Apple Numbers

Hi

I record daily totals against a date. The date entered is always understood by Numbers to be the start of the day. This is not unexpected. So typing 21/12/2024 is actually 21/12/2024 00:00:00. Adding 1s gives 21/12/2024 00:00:01 and subtracting 1s gives 20/12/2024 23:59:59.

On occasion I need to record the date and the actual time so I might specifically enter 21/12/2024 15:08:00.

I then need to do some date math, often comparing dates and times but of course the date I entered for the end of the day figures, 21/12/2024, is in reality actually less than 21/12/2024 15:08:00 so confuses my results.

What are my workaround options?

Always enter a date and time exactly as I need them, so 21/12/2024 23:59:59 or 21/12/2024 15:08:00.
Use a helper column to convert 21/12/2024 00:00:00 to 21/12/2024 23:59:59 and copy 21/12/2024 15:08:00 in as it is and use this new column in my math? [This is my current approach]

Are there any other good ways to address this problem?

Thank you for your help.

[Re-Titled by Moderator]

Mac Studio, macOS 15.2

Posted on Dec 21, 2024 7:18 AM

Reply

Answer 1

Top-ranking reply

Yellowbox

Level 7

21,987 points

Dec 21, 2024 8:32 AM in response to iwaddo

Hi iwaddo,

The function TIMEVALUE is your friend.

The TIMEVALUE function returns the time as a decimal fraction of a 24-hour day for a given date/time value or time string.

Formula in C2 is IF(TIMEVALUE(B2)=0,B2+DURATION(,,23,59,59),B2)

If the time in B2 is midnight on that date (you inserted no explicit time) then the TIMEVALUE is 0. So, add a duration of 23:59:59 else copy B2 as is.

DURATION(,,23,59,59) means zero weeks, zero days, 23 hours, 59 minutes, 59 seconds.

Regards,

Ian.

Reply

Answer 2

iwaddo Author

Level 1

126 points

Dec 21, 2024 9:23 AM in response to Yellowbox

That is really helpful thank you.

Can I also ask why the result of a SUMIFS changes depending on the format of the date I am using.

This is my formula =SUMIFS('Charge (kWh)',Charging End,">="&A2,Charging End,"<="&B2+DURATION(0,0,23,59,59,0))

Both A2 and B2 are dates. What I cannot workout is why the result changes based on the format of B2 If B2 is formatted dd/mm/yyyy h:mm:ss I get the expected result but if I change the format of B2 to dd/mm/yyyy the result changes to something unexpected.

I really cannot work out why the format of a date used as input should affect the result. Any guidance appreciated.

Reply

Answer 3

Yellowbox

Level 7

21,987 points

Dec 22, 2024 3:50 AM in response to iwaddo

Hi iwaddo,

I do not understand what your formula is trying to do:

SUMIFS('Charge (kWh)',Charging End,">="&A2,Charging End,"<="&B2+DURATION(0,0,23,59,59,0))

Where does that formula live?

Where is 'Charge (kWh)'?

Where is Charging End?

Why include DURATION(0,0,23,59,59,0 if the Date & Time already has an actual time entered?

🤔

Go to Menu > Numbers > Settings > General > Cell References and turn off (untick) "Use header name as labels".

Header names as labels may make sense to the author (you) but such cell references are difficult for others (we in this forum) to fathom.

Screen shots, please!

Regards,

Ian.

Reply

Answer 4

iwaddo Author

Level 1

126 points

Dec 22, 2024 4:12 AM in response to Yellowbox

Sorry, I can see why that is very confusing and very unhelpful.....

I've posted a variation on this question here Unexpected Numbers Result without Helper … - Apple Community

I am trying to sum values based on Date. Most of the time the Time of the Date defaults to 00:00:00, the start of the day. Occasionally the Date has a real Time that I've entered. So I might have 22/12/2024 00:00:00 and 21/12/2024 15:32:00.

If I am trying to SUMIFS a value based on <= the Date in the current row and >= Date -7+1 (7 days earlier).

The SUMIFS is correct in the way it compares the Dates & Times but for the purpose of my math I really want the default Time of 00:00:00 to be 23:59:59, so not the start of the day but the end of the day.

I can either add a helper column to convert all Dates & Times where the Time is 00:00:00 to 23:59:59 or I can try and do this math in the formula by adding the Duration.

Not sure if this helps, but I really appreciate you tying to help, thank you.

Reply

Converting start-of-day dates to end-of-day in Apple Numbers

Similar questions