Calculations using time without dates!
I have a table, called “Work Records”, where I track lessons I provide.
In column A I record the date of the lesson.
I record the start and end times in columns D & E, respectively. There may be multiple entries for the same date, with different start times. Start times can be, for example, 09:00, 09:15: 09:30, ….
In column F I calculate the duration by subtracting D from E, within a DUR2HOURS formula. Durations are always whole numbers - no fractions of an hour.
Columns D & E are formatted for time only (Date set to “None”), but when I select a cell in these columns, Actual still shows a date and time.
In another table, called “Start - Duration”, I want to see how the duration varies with the start time.
Columns B & C of “Start - Duration” are formatted for time only (Date set to “None”), but when I select a cell in these columns, Actual still shows a date and time (in this case, the date when the table was created). Normally, columns B & C are hidden.
Columns D through G are the possible durations (1 to 4 hours). In cells D2 through G8 I want to calculate the number of times a lesson starts within the period HH:00 and HH:59 and lasts for 1, 2, 3 or 4 hours. I use the formula
COUNTIFS($Start Time,">="&$B2,$Start Time,”<=“&$C2,$Duration,"="&D$1),
where “Start Time”, and “Duration” refer to columns D and F of the “Work Records” table.
There are no errors reported for the formula, but as can be seen, the calculations show “0”. I suspect this is due to the hidden dates being included in the time-only columns in both tables, which will be different (in “Start - Duration” the date the table was created, in “Work Records” the date the lesson was booked).
What do I need to do to make the “Start - Duration” table work the way I want?
NOTE that I have selected my MBP as the device and Numbers for Mac as the topic, but this is an issue for me with Numbers across the MBP, iPhone and iPad.
MacBook Pro 15″, macOS 10.15