Calculating Hours and Travel Time

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:

Screen Shot 2021-08-27 at 1.51.41 PM.png

Cheers,

Genevieve

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?

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

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.

The structure and math of the formula is working correctly. 🙂

I've tested in my own sheet and it worked as expected when only 1 row in the sheet had "Time In Lieu" specified for January at 0.5.

Can you double check the source sheet data? Is it possible that there's another January row (e.g. from 2024) that is recording the number to SUM as a duplicate?

All comments

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

Andrée Starå

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!

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.

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:

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:

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

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

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?

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