Calculating Hours and Travel Time
Answers

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.

No problem! I'm glad you were able to get this resolved! 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
@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"))))

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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
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"))))

And one more to include subtraction of time in lieu:
=SUM([Travel To Site]@row:[Travel From Site]@row))
Thank you @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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
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.

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)
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
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.

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

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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
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

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
 Mulitselect 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.
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
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
Help Article Resources
Categories
Check out the Formula Handbook template!