create a Date & Time value from separate date & time text strings

Simple. Your calculation is wrong.

Looking at the cell Table 1::A2, the formula can be broken down into components:

DATE() + " " + TIME()

This creates a TEXT object that might look like a date, but it's really just text, which is why you can't use date calculations on it.

Instead, this function needs to combine a DATE() plus a number of hours/minutes/seconds using the DURATION() function:

=DATE(RIGHT(),LEFT(),MID()) + DURATION(0,0,LEFT(Date-Time::B2,2),RIGHT(Date-Time::B2,2),0,0)

(RIGHT/LEFT/MID parameters omitted for clarity)

This adds the hours/minutes to the DATE() calculated from the date field.

What you end up with is a valid DateTime field and can use the normal Format -> Cell -> Date & Time options to display whichever date/time components you want.

I have not been able to make your technique work.

Treating the date and time as if they are strings does return a result that can then be formatted as a date. For example, A2&" "&B2 will return a result that you can then format as a date and operated on as such.

HTH,

Bob

> Question tho: I built the tables using the same values and saw the same results with the dates formatted as dates but Numbers clearly saw it as text. That puzzles me. Can you help with that?

Do you mean in your original Table 1::A2 cell?

The date is formatted correctly because it is a date - or at least it was.

Let's take a simplified version:

=DATE(2025,1,1) & " " & TIME(9,50,0)

The DATE() function takes a value for year, month and day, and returns a valid date object. By default, based on your system internationalization settings, this will look like 1/1/2025 (the 00:00:00 is not relevant for this discussion)

Similarly, the TIME() function will take a value for hours, minutes and seconds, and return a valid time object such as 9:50:00 am

The issue is that you combined that DATE() field with a string ( & " " & ).

At this point, the DATE() field has no option but to be converted to a text object since you're appending a space to it. That's why the date part looks right, but it is treated as a text object as far as Numbers is concerned, and you lose any date-related functionality.

You then further add the time object to the end of that string and that forces the time object to lose any concentration of datetime functionality... it's just another string of characters.

"1/1/2025 09:50:00" (the specific date and time formatting (12h, 24h, am/pm, etc.) will depend on your system settings). To you and I, we see a date, because that's how our brains work. To Numbers, though, it's just a stream of characters.

Compare that with my version - I used your existing logic to parse the valid DATE() object, to which I added a number of hours and minutes via the DURATION() function. This is still a valid date object., thus solving your problem.

Before calling this done, the dates you are creating are not 2023 and 2024. They are the 0023 and 0024. In the DATE function replace RIGHT(A,2) with "20"&RIGHT(A,2)

Interesting. If your result is not a DATE how were the "/" and ":" characters inserted?

Something to try: Copy one cell then paste into that same cell using Ctrl-Cmd-V to overwrite the formula. Then try reformatting the result to a date.

I learned something new! Thank you! Plus the result can be reformatted in Numbers without having to copy and paste! Nice

Question tho: I built the tables using the same values and saw the same results with the dates formatted as dates but Numbers clearly saw it as text. That puzzles me. Can you help with that?

Yes, I was referring to cell A2 of Table 1.

I thought the space was necessary to separate the date and time but, given your input, it actually caused the error.

I'll alter my approach to this issue and expect the results will be more useful.

Thank you,

Bob

Correction: The key combination is Shift-Cmd-V. My apology!