Calculating Hours and Travel Time

Michelle Maas

Hi @Genevieve P. ah I understand now. The original formula calculates a sum per the row but in order to reference this sheet and calculate the overall column entries is not possible unless I break the formula per column into individual cells and then sum those. Thanks for your help.

Genevieve P.

No problem! I'm glad you were able to get this resolved! 🙂

Michelle Maas

@Genevieve P. I want to edit the formula discussed here to not include Time in Lieu. Below is the original formula:

=(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)))

Now I want to add to the formula the part to not include "Time in Lieu", but not sure how to go about it. I'm thinking something like the below but I'm getting #UNPARSEABLE:

=(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), [Project # | Activity]@row, NOT(HAS(@cell,"Time in Lieu"))))

Genevieve P.

Hey @Michelle Maas

Do you mean that you need to fully ignore any row that says "Time in Lieu"?

If so, you'll want to add that statement as the very first thing on your formula:

=IF(HAS([Project # | Activity]@row, "Time in Lieu"), 0, rest of the formula

Michelle Maas

Excellent, that worked perfectly! Thank you @Genevieve P.

If I wanted to subtract Time in Lieu from the total hours, how would I include the subtraction in the same formula?

=(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), [Project # | Activity]@row, NOT(HAS(@cell,"Time in Lieu"))))

Michelle Maas

And one more to include subtraction of time in lieu:

=SUM([Travel To Site]@row:[Travel From Site]@row))

Thank you @Genevieve P.

Genevieve P.

Hey @Michelle Maas

I'm assuming you have a column in your sheet titled "Time in Lieu" with a number in it, is that correct?

If so, for your second formula you can simply use - to subtract a value:

=SUM([Travel To Site]@row:[Travel From Site]@row) - [Time in Lieu]@row

Same thing with your other formula: you can add - [Time in Lieu]@row at the end, after it has created all of the calculations needed:

=(IF(formula)) - [Time in Lieu]@row

Michelle Maas

Hey @Genevieve P. apologies for not being clear - there is a column that shows the Project # | Activity and this includes a drop down selection for Time in Lieu, so the reporting is picking up the different words depending on what it is looking for. In this case, I just want to subtract Time in Lieu from the overall totals.

Genevieve P.

Hey @Michelle Maas

Is your overall total looking at the column where you have the SUM function?

=SUM([Travel To Site]@row:[Travel From Site]@row))

So, do you want the row that has "Time in Lieu" selected to show 0 for that row, so it's not included in your report?

=IF(HAS([Project # | Activity]@row, "Time in Lieu"), 0, SUM([Travel To Site]@row:[Travel From Site]@row)

Michelle Maas

The SUM formula is picking up all time that is allocated to the different Activities. However, I want the Time in Lieu to be subtracted from the total SUM as the extra hours have already been worked and if I show TIL, it is duplicating the hours. For the other formula, it is specifically working out the total SUM but adjusting for travel. This total too needs to be less TIL.

Michelle Maas

Hey @Genevieve P. I realised I was working in the wrong sheet as the way it was set up the formula would not work. In another sheet I am calculating the total hours for the month:

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

In this formula I want to subtract any Time in Lieu from the Total PAYE for the month of January, perhaps something like below which is not producing the results I want:

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

Genevieve P.

Hi @Michelle Maas

Thanks for the clarification! This definitely helps.

If you're just looking to ignore any of the rows that have "Time in Lieu" selected as one of the options, so it's not included in the total, you can use a single SUMIFS for this.

The criteria would be that the {Activity} column does not have "Time in Lieu" selected.

Try something like this:

=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")))

Let me know if this does it for you!

Cheers,

Genevieve

Michelle Maas

Hey @Genevieve P.

Thank you for getting back to me. To clarify for this calculation I need to subtract the Time in Lieu, not ignore it. 😀

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

Genevieve P.

Hey @Michelle Maas

Yes exactly! 🙂

The SUMIFS is summing together rows that meet your criteria (the date). Ignoring the rows that have "Time in Lieu" remove them from the criteria, or subtract them from the total.

Think of it like a filter - you've put a filter on the source sheet. Your two criteria:

• Month is 1
• Mulit-select does not have "Time in Lieu"

Then it totals the other column.

In fact, this may be easier to do in a Report, using a filter just like that and the Summarize function on the other column.

Michelle Maas

Hey @Genevieve P.

The way time in lieu works is that if a person works an extra hour, they take that hour off at another time, i.e. I work an extra hour on Monday, and on Friday I leave the office an hour earlier. In doing a TIL process, this cancels out the extra hours worked and is used instead of paying overtime. Therefore, I need the total monthly sum to subtract the TIL so that it cancels out the overtime and shows the true time worked. What the formula below is doing is it is calculating all the time worked including any overtime for the month of January, and then I want it to subtract from that total any time taken in lieu (hours that are not worked). Hopefully that is clearer?

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