Sum of time(Duration) based on dates

Question

Level 1

8 points

Sum of time(Duration) based on dates

I feel like I'm using the correct formula but it is not adding my time. So as you can see in my test table I am trying to get the total hours worked from the first week of next year. Those hours are located in my log table in column H and the date I worked those hours are in column C. This is for the coming years work. I am doing this on a MacBook Pro but ultimately will be using it on my iPhone 11. I have something similar now but trying to make improvements. Can you give me any ideas? I have my dates formatted the same, I also have the duration formatted the same.

Posted on Nov 3, 2020 8:17 PM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

31,162 points

Nov 4, 2020 1:46 PM in response to BigOleOak

Another thing I now see is the >= symbol is a single character vs the two characters > and =. Same for the <= character. Numbers converts those in formulas to a single character but it doesn't seem to like it in the quotes.

Numbers can be touchy when it comes to dates. It would be better if you used the DATE function instead of writing the dates out as strings.

=SUMIFS(Log::H,Log::C,">="&DATE(2020,12,28),Log::C,"<="&DATE(2021,1,3))

Reply

Answer 2

Badunit

Level 7

31,162 points

Nov 3, 2020 8:45 PM in response to BigOleOak

I see an apostrophe before Dec 28, 2020 in your formula. Try removing it.

Reply

Answer 3

Barry

Level 9

60,144 points

Nov 4, 2020 2:26 PM in response to BigOleOak

When dealing with conditions involving Date and Time Values, I've often found it useful to use references to a cell containing the D&T value, rather than writing it into the fomula. Here's an example, using two new columns on the Test table to hold those dates, and the revised version of your formula, displayed below the tables.

The two added columns on Test may be shown or hidden. My own choice would likely be to hide column S (showing the end of week date) but displaying column B, with the header label as shown.

Formulas (all on Test):

A2: SUMIFS(Log::H,Log::C,">="&B2,Log::C,"<="&C2)

B2: contains an entered date.

B3: B2+7

C2: B2+6

Each formula is filled down from its entry cell to the last row of Test. Note that the Entry cell in column B is B3

Regards,

Barry

Reply