Calculating Hours and Travel Time
The above screenshot is from a time report that I would like some help with.
The Total column calculates all the columns in the row, the final column calculates the Total PAYE.
The Total PAYE formula is as follows:
=([Travel To Site]@row - 0.75) * 1 + [Std Hrs]@row * 1 + 1.5 * [OOH (1.5x)]@row + 2 * [OOH (2x)]@row + ([Travel From Site]@row - 0.75) * 1
I would like the Total PAYE to include a calculation in the formula as follows:
a) Std Hrs x1 + OOH x1.5 + OOH x2, and
b) subtract the first 45 minutes of [Travel To Site] and [Travel From Site], and
c) ignore blanks (no travel time), and
d) add any additional overtime hours (more than .75) spent travelling to the calculation in a).
If the travel time is less than .75, I also need the formula to treat this as .75. So if they travel for .25 or .50, this would calculate as part of the initial .75 of travel time.
Best Answers
-
The first thing we'll want to do is check for Blanks in either of the travel columns and turn that to 0 instead of -0.75.
I did this by embedding in an IF statement around each Travel statement:
=(IF([Travel to Site]@row = "", 0, [Travel to Site]@row - 0.75) + [Std Hrs]@row + 1.5 * [OOH (1.5x)]@row + 2 * [OOH (2x)]@row + (IF([Travel From Site]@row = "", 0, [Travel From Site]@row - 0.75)))
But then you also note that if travel time is not blank, but under 0.75, it should be seen as 0.75. We'll add that into each of these IF statements as well:
=(IF([Travel to Site]@row = "", 0, IF([Travel to Site]@row <= 0.75, 0.75, [Travel to Site]@row) - 0.75) + [Std Hrs]@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)))
Note - I removed out the *1 as I found them redundant, but we can add them in if you prefer.
Does this create the calculation you're looking for?
Here's an example of my sheet with the formula:
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
My apologies, I'm still unsure as to what the end formula is doing. However if you're looking to total each column but only for a specific project, you could use a SUMIFS for each individual column. Meaning in your Metric sheet, you would perform each calculation separately, then you could have a different cell create another calculation off of that, if needed.
=SUMIFS({Std Hrs}, {Project Number Column}, [Project Number]@row)
in another cell:
=SUMIFS({OOH * 1.5}, {Project Number Column}, [Project Number]@row)
Etc.
Keep in mind that each of {these ranges} are now looking at an entire column, instead of at one single cell. This changes how formulas work.
Does that help?
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
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 -
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.
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
I think I am getting closer to the answer but it is still showing an error - #UNPARSEABLE:
=IF(OR(ISBLANK([Travel To Site]@row), ISBLANK([Travel From Site]@row)), "", IF([Travel To Site]@row) < 0.75, 0.75, IF([Travel From Site]@row) < 0.75, 0.75, ([Travel To Site]@row - 0.75) * 1 + [Std Hrs]@row * 1 + 1.5 * [OOH (1.5x)]@row] + 2 * [OOH (2x)]@row + ([Travel From Site]@row - 0.75) * 1
-
Or is it possible to combine the less than within the subtraction part of the formula, as in:
=IF(OR(ISBLANK([Travel To Site]@row), ISBLANK([Travel From Site]@row)), "", ([Travel To Site]@row <= 0.75, - 0.75) * 1 + [Std Hrs]@row * 1 + 1.5 * [OOH (1.5x)]@row] + 2 * [OOH (2x)]@row + ([Travel From Site]@row <= 0.75, - 0.75) * 1
I'm still getting #UNPARSEABLE, so something is not quite right...
-
Getting closer but now error is #INCORRECT:
=SUMIFS(AND(NOT(ISBLANK([Travel To Site]@row, [Travel From Site]@row, >0.75, =0.75), IF(OR([Travel To Site]@row, [Travel From Site]@row, <=0.75, -0.75) * 1 + [Std Hrs]@row * 1 + 1.5 * [OOH (1.5x)]@row + 2 * [OOH (2x)]@row))))
-
Still #INCORRECT
=SUMIFS([Travel To Site]@row + [Std Hrs]@row + [Travel Site To Site]@row + 1.5 * [OOH (1.5x)]@row + 2 * [OOH (2x)]@row + [Travel From Site]@row, IF(NOT(ISBLANK([Travel To Site]@row, [Travel From Site]@row, <0.75, "0.75"), IF(AND([Travel To Site]@row, [Travel From Site]@row, >=0.75, -0.75)))))
-
I hope you're well and safe!
I'd be happy to take a quick look.
Can you maybe share the sheet(s)/copies of the sheet(s)? (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 day!
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
The sheet is too big and sensitive to share. Only need the formula for the columns I have already shared in image. Hope you can help. Thanks.
-
As @Andrée Starå @Michelle Maasmentioned we need only a sample copy of your sheet with sample data ( remove any sensitive data ) because it will help to use the same columns name and check the columns type to create the correct formula for you.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
The first thing we'll want to do is check for Blanks in either of the travel columns and turn that to 0 instead of -0.75.
I did this by embedding in an IF statement around each Travel statement:
=(IF([Travel to Site]@row = "", 0, [Travel to Site]@row - 0.75) + [Std Hrs]@row + 1.5 * [OOH (1.5x)]@row + 2 * [OOH (2x)]@row + (IF([Travel From Site]@row = "", 0, [Travel From Site]@row - 0.75)))
But then you also note that if travel time is not blank, but under 0.75, it should be seen as 0.75. We'll add that into each of these IF statements as well:
=(IF([Travel to Site]@row = "", 0, IF([Travel to Site]@row <= 0.75, 0.75, [Travel to Site]@row) - 0.75) + [Std Hrs]@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)))
Note - I removed out the *1 as I found them redundant, but we can add them in if you prefer.
Does this create the calculation you're looking for?
Here's an example of my sheet with the formula:
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@Genevieve P. Thank you so much for your help. That was exactly what I was looking for. Appreciate your help with this. 🙏
-
No problem, I'm glad this works for you! 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@Genevieve P. - I want to use the above formula in a referenced sheet but keep getting #INVALID OPERATION. I've tried SUMIF(S) but it's not working. Please can I get your help with the formula and then I want to add a reference for specific project numbers to be picked up from the reference sheet, matched to a cell in the local sheet:
=(IF({NAME 2022 - Travel To Site} = "", 0, IF({NAME 2022 - Travel To Site} <= 0.75, 0.75, {NAME 2022 - Travel To Site}) - 0.75) + {NAME 2022 - Std Hrs} + {NAME 2022 - Site To Site} + 1.5 * {NAME 2022 - OOH (1.5x)} + 2 * {NAME 2022 - OOH (2x)} + IF({NAME 2022 - Travel From Site} = "", 0, IF({NAME 2022 - Travel From Site} <= 0.75, 0.75, {NAME 2022 - Travel From Site}) - 0.75))
=(IF({NAME 2022 - Travel To Site} = "", 0, IF({NAME 2022 - Travel To Site} <= 0.75, 0.75, {NAME 2022 - Travel To Site}) - 0.75) + {NAME 2022 - Std Hrs} + {NAME 2022 - Site To Site} + 1.5 * {NAME 2022 - OOH (1.5x)} + 2 * {NAME 2022 - OOH (2x)} + IF({NAME 2022 - Travel From Site} = "", 0, IF({NAME 2022 - Travel From Site} <= 0.75, 0.75, {NAME 2022 - Travel From Site}) - 0.75), {NAME 2022 - Activity}, [Project Number]@row)
-
The previous formula was calculating on a row-by-row basis, looking at individual cells on each row.
Can you clarify what it is you're looking to do with the cross-sheet formula? It sounds like it would be best to keep the current columns in your first sheet then use a SUMIFS or formula looking at those formula columns (instead of evaluating the columns that are referenced in your first formula). Does that make sense?
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Hi @Genevieve P. , thanks for getting back to me. I want to total columns on a time report but only for a specific project. I am building a metrics sheet to calculate the time spent on each project, with a breakdown of each column:
= {Std Hrs}, {OOH * 1.5}, {OOH * 2}, {Total Travel}, {Total PAYE}
So the reference sheet is a time report, which has hours allocated against different projects in the {Activity} column. I want to calculate in the metrics sheet just the travel hours in this case:
{Travel To Site} subtract 45 minutes + {Travel Site To Site} + {Travel From Site} subtract 45 minutes
-
I'm thinking it may be something like this but getting #UNPARSEABLE:
=SUMIFS({Troy Roe 2022 - Activity}, [Project Number]@row), IF({Troy Roe 2022 - Travel To Site} = "", 0, IF({Troy Roe 2022 - Travel To Site} <= 0.75, 0.75, {Troy Roe 2022 - Travel To Site}) - 0.75) + {Troy Roe 2022 - Site To Site} + IF({Troy Roe 2022 - Travel From Site} = "", 0, IF({Troy Roe 2022 - Travel From Site} <= 0.75, 0.75, {Troy Roe 2022 - Travel From Site}) - 0.75))
-
My apologies, I'm still unsure as to what the end formula is doing. However if you're looking to total each column but only for a specific project, you could use a SUMIFS for each individual column. Meaning in your Metric sheet, you would perform each calculation separately, then you could have a different cell create another calculation off of that, if needed.
=SUMIFS({Std Hrs}, {Project Number Column}, [Project Number]@row)
in another cell:
=SUMIFS({OOH * 1.5}, {Project Number Column}, [Project Number]@row)
Etc.
Keep in mind that each of {these ranges} are now looking at an entire column, instead of at one single cell. This changes how formulas work.
Does that help?
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!