Calculating Hours and Travel Time

Options

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

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Michelle Maas

    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

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Michelle Maas

    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?

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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.

«13

Answers

  • Michelle Maas
    Michelle Maas ✭✭✭✭
    Options

    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

  • Michelle Maas
    Michelle Maas ✭✭✭✭
    Options

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

  • Michelle Maas
    Michelle Maas ✭✭✭✭
    Options

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

  • Michelle Maas
    Michelle Maas ✭✭✭✭
    Options

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

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @Michelle Maas

    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.

  • Michelle Maas
    Michelle Maas ✭✭✭✭
    Options

    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.

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    @Michelle Maas

    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.

    PMP Certified

    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"

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Michelle Maas

    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

  • Michelle Maas
    Michelle Maas ✭✭✭✭
    Options

    @Genevieve P. Thank you so much for your help. That was exactly what I was looking for. Appreciate your help with this. 🙏

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    No problem, I'm glad this works for you! 🙂

  • Michelle Maas
    Michelle Maas ✭✭✭✭
    Options

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

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Michelle Maas

    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?

  • Michelle Maas
    Michelle Maas ✭✭✭✭
    Options

    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

  • Michelle Maas
    Michelle Maas ✭✭✭✭
    Options

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

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Michelle Maas

    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?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!