Calculating Hours and Travel Time

Michelle Maas

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.

Genevieve P.

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.

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.

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.

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.

Michelle Maas

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

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

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

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å

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!

Michelle Maas

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

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

Genevieve P.

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

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

Genevieve P.

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

Michelle Maas

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

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

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

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.

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?