Getting "Date" and "Time" entries to have the same calendar date in Numbers.

Question

lewbjr Author

Level 1

4 points

Getting "Date" and "Time" entries to have the same calendar date in Numbers.

I have a Numbers spreadsheet that has the following columns:

Column A - Date (formatted as Date & Time but no display of time)

Column B - Start Time (formatted as Date & Time but no display of date)

Column C - End Time (formatted as Date & Time but no display of date)

I am entering data to test and noticed that if I enter a Date in Column A of say 10/1/2022 and a Start Time of 9:00 am in Column B the date associated with Column B is today's date. The same goes with Column C End Time.

The question is how do I associate Column B and Column C to Column A so they will all have the same date parameter no matter when I enter the data?

MacBook Pro 13″, macOS 12.4

Posted on Sep 4, 2022 3:07 PM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

28,726 points

Sep 4, 2022 3:58 PM in response to lewbjr

Do you mean "when I use them in a function I want B and C to have the same date as column A" and/or "when I use them in formulas I don't want the different dates to mess up the calculations"?

To put A's date on B2 for use in formulas and charts

A+TIMEVALUE(B)-TIMEVALUE(A)

Use that instead of just B

You can put this in a new column and have your formulas/charts refer to this new column (and similarly for C) so your formulas don't get too messy. Hide the columns after all is set up and running.

If you want to subtract B from C (end time minus start time) and the answer to be in decimal hours with the assumption that the two times are from the same day, you do not need to convert their dates, just remove the dates

=24*(TIMEVALUE(C)-TIMEVALUE(B))

You could create two new columns for 24*TIMEVALUE(B) and 24*TIMEVALUE(C).

I don't know what you need to do exactly so I'm just posting things that might help.

Reply