Calculating Hours and Travel Time

Genevieve P.

Aha! Yes, that does make sense.

So you first need to SUM all the values without the Time in Lieu (current suggested formula) then you need to SUM all the Time in Lieu values separately and subtract it.

If you simply subtract the Time in Lieu value now from the general total, it would cancel out itself and ignore those rows, versus also taking that total and removing it.

(Total without Time in Lieu) - (Total OF Time in Lieu)

So first formula:

=SUMIFS({Michelle Maas 2023 - Total PAYE}, {Michelle Maas 2023 - Date}, IFERROR(MONTH(@cell), 0) = 1,{Michelle Maas 2023 - Activity}, NOT(HAS(@cell, "Time in Lieu")))

then subtract the second formula:

- SUMIFS({Michelle Maas 2023 - Total PAYE}, {Michelle Maas 2023 - Date}, IFERROR(MONTH(@cell), 0) = 1,{Michelle Maas 2023 - Activity}, HAS(@cell, "Time in Lieu"))

Like that?

Michelle Maas

Hey @Genevieve P.

Thanks for sticking with me on trying to get this right. I tried the formulas above and I'm getting #UNPARSEABLE. Reviewing what you have written, I think these formulas are designed for two different columns. I only have one cell in the report for each month, and for each person. Do I now need to create a separate column in the referenced sheet to do an additional formula calculation to make the reporting sheet formula work?

Referenced sheet is the time report where the hours are added on one row and allocated to the various activities. The reporting sheet (below screenshot) is to calculate PAYE, which is where I need to subtract the TIL:

=SUMIFS({Michelle Maas 2023 - Total PAYE}, {Michelle Maas 2023 - Date}, IFERROR(MONTH(@cell), 0) = 1)

Genevieve P.

Hi @Michelle Maas

Can you post the formula you're using?

This should be one formula all together:

=(Total without Time in Lieu) - (Total OF Time in Lieu)

Which translates to:

=SUMIFS() - SUMIFS()

So in your case:

=SUMIFS({Michelle Maas 2023 - Total PAYE}, {Michelle Maas 2023 - Date}, IFERROR(MONTH(@cell), 0) = 1,{Michelle Maas 2023 - Activity}, NOT(HAS(@cell, "Time in Lieu"))) - SUMIFS({Michelle Maas 2023 - Total PAYE}, {Michelle Maas 2023 - Date}, IFERROR(MONTH(@cell), 0) = 1,{Michelle Maas 2023 - Activity}, HAS(@cell, "Time in Lieu"))

Michelle Maas

Hey @Genevieve P.

This is the formula I am using:

=SUMIFS({Michelle Maas 2023 - Total PAYE}, {Michelle Maas 2023 - Date}, IFERROR(MONTH(@cell), 0) = 1)

The formula for the Total PAYE is from the referenced sheet, and it would not make sense to adjust that as the time is recorded on individual rows depending on activity, and then the reporting sheet is calculating PAYE hours for the month, so this is where the subtraction needs to take place, in the reporting sheet:

=(IF([Travel To Site]@row = "", 0, IF([Travel To Site]@row <= 0.75, 0.75, [Travel To Site]@row) - 0.75) + [Std Hrs]@row + [Travel Site To Site]@row + 1.5 * [OOH (1.5x)]@row + 2 * [OOH (2x)]@row + (IF([Travel From Site]@row = "", 0, IF([Travel From Site]@row <= 0.75, 0.75, [Travel From Site]@row) - 0.75)))

Surely there must be an easier way than the long formula as I just keep getting #UNPARSEABLE when I use the formula you have given above. An alternative solution may be to have a hidden column in the time report that automates any TIL entry to become a subtraction i.e. 1.00 = -1.00, and then the reporting sheet formula can pick up this subtraction column but I am sure there must be a better solution.

Genevieve P.

Hey @Michelle Maas

It makes a big difference seeing this source sheet - I can see now that your Project # Activity column is not a multi-select, which simplifies the formula a lot (we don't need HAS at all).

With your current set-up, we need to SUM together all the rows that are in a specific month and are not Time in Lieu. Then, from that total, we subtract the SUM of all the rows that are Time in Lieu, but also within that same month.

=(Total without Time in Lieu) - (Total OF Time in Lieu)

or

=SUMIFS({SUM Column}, {Date}, Month = 1, {Activity}, <> "Time in Lieu") - SUMIFS({SUM Column}, {Date}, Month = 1, {Activity}, = "Time in Lieu")

Does that make sense?

=SUMIFS({Michelle Maas 2023 - Total PAYE}, {Michelle Maas 2023 - Date}, IFERROR(MONTH(@cell), 0) = 1,{Michelle Maas 2023 - Activity}, <> "Time in Lieu")) - SUMIFS({Michelle Maas 2023 - Total PAYE}, {Michelle Maas 2023 - Date}, IFERROR(MONTH(@cell), 0) = 1,{Michelle Maas 2023 - Activity}, = "Time in Lieu")

If this is still giving you an error, can you show the formula you've put in the cell? This is the formula I'd like to see in your sheet in order to see where the error may be coming in 🙂

I'll follow-up in a new comment to your second suggestion.

Genevieve P.

For your second suggestion, yes! We could make any rows with "Time in Lieu" negative, so you can keep your original SUMIFS formula.

What we would do here is IF the Project says "Time in Lieu" we multiply your result by -1. Otherwise, we multiply it by 1, to keep it positive.

* IF([Project # | Activity]@row = "Time in Lieu", -1, 1)

=(IF([Travel To Site]@row = "", 0, IF([Travel To Site]@row <= 0.75, 0.75, [Travel To Site]@row) - 0.75) + [Std Hrs]@row + [Travel Site To Site]@row + 1.5 * [OOH (1.5x)]@row + 2 * [OOH (2x)]@row + (IF([Travel From Site]@row = "", 0, IF([Travel From Site]@row <= 0.75, 0.75, [Travel From Site]@row) - 0.75))) * IF([Project # | Activity]@row = "Time in Lieu", -1, 1)

Then in your second sheet you would keep your original formula without adjusting the filters or subtracting another SUM.