Referenced Sheet Hours Calculation Incorrect

Michelle Maas

I have a time report that works on the time method of 1 = 1 hour, 0.25 =15 minutes, 0.50 = half an hour, 0.75 = 45 minutes, etc. If I do a SUM calculation within the sheet, I get the correct amount of time i.e. 37.50 for one week. However, on a metrics sheet, I am trying to calculate from this referenced time report, the monthly and weekly time, and I get a completely different amount i.e. 29.50. Why would it be doing this and how do I fix it?

Genevieve P.

Hi @Michelle Maas

It looks like you may be missing the = sign before your Start Date, which means it's only looking at greater than Jan 1st, or Jan 4th. Should that date be included in the range?

If so, try adding the =:

=SUMIFS({Hours Column}, {Date Column}, AND(@cell >= DATE(2021, 1, 1), @cell <= DATE(2021, 1, 31), ISDATE(@cell)))

=SUMIFS({Hours Column}, {Date Column}, AND(@cell >= DATE(2021, 1, 4), @cell <= DATE(2021, 1, 8), ISDATE(@cell)))

Cheers,

Genevieve

Andrée Starå

Hi @Michelle Maas

I hope you're well and safe!

Strange!

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Michelle Maas

For the month formula:

=SUMIFS({Hours Column}, {Date Column}, AND(@cell > DATE(2021, 1, 1), @cell <= DATE(2021, 1, 31), ISDATE(@cell)))

For the week formula:

=SUMIFS({Hours Column}, {Date Column}, AND(@cell > DATE(2021, 1, 4), @cell <= DATE(2021, 1, 8), ISDATE(@cell)))

The Hours Column has a SUM formula for the row, capturing the types of hours columns:

=SUM([Type of Hours Start]@row:[Type of Hours End]@row)

Genevieve P.

Hi @Michelle Maas

It looks like you may be missing the = sign before your Start Date, which means it's only looking at greater than Jan 1st, or Jan 4th. Should that date be included in the range?

If so, try adding the =:

=SUMIFS({Hours Column}, {Date Column}, AND(@cell >= DATE(2021, 1, 1), @cell <= DATE(2021, 1, 31), ISDATE(@cell)))

=SUMIFS({Hours Column}, {Date Column}, AND(@cell >= DATE(2021, 1, 4), @cell <= DATE(2021, 1, 8), ISDATE(@cell)))

Cheers,

Genevieve

Michelle Maas

@Genevieve P. Wow, such a simple fix! Thank you, it works great now.

Genevieve P.

No problem at all! 🙂