Formula Help

FlyFlip

Hello,

I need help with a formula that calculates time off in days and a formula for time off in hours.

Time Off Hours - I'd like the "Time Off Days" to say 0 or stay blank if there is no "end date".

Time Off Days - I'd like the "Time Off Days" to calculate the time off in days based on the "Start Date" and "End Date". Also, I'd like it to calculate the time off in hours as well in a separate column.

Thanks ahead of time for your help!

Genevieve P.

Hi @FlyFlip

Can you post a picture of how you put in the first formula? It sounds like perhaps a comma or quote is in the wrong place.

For the second formula, this should be put in a new column, one that Paul called "Time Off Hours Helper", versus putting it in the Time Off Hours column. Does that make sense?

Paul Newcome

The time off days formula looks like I may have typed in one of your column names incorrectly. Make sure the column names are spelled how you have them in your sheet.

The second formula goes into a separate column as it looks like you are potentially going to have some sort of manual entry in the existing hours column.

Genevieve P.

Hi @FlyFlip

Can you clarify how you're calculating the "Time Off Hours"? Is it that if there's no End Date selected, and there's only a Start Date, the time off is automatically "4 hours"?

If so, then yes, we can do it all in one column:

=IF([End Date]@row = "", 4, 0) + IF([Time Off Days]@row = "", 0, [Time Off Days]@row * 8)

Note that this is multiplying the number of days off by 8, assuming an 8 hour work day. If you mean to put this as a 4 hour work day, then you'd want to adjust the formula to multiply b 4 instead:

=IF([End Date]@row = "", 4, 0) + IF([Time Off Days]@row = "", 0, [Time Off Days]@row * 4)

However if the Time Off Hours will vary each day (e.g. someone may put in a Start Date but only have 2 hours off that day) there's no way for the formula to tell this. You would need the "Time Off Hours" column to be manually input, and then you can use the Time Off Hours Help column to create your calculation.

For example:

=IF([Time Off Days]@row = "", [Time Off Hours]@row, 0) + IF([Time Off Days]@row = "", 0, [Time Off Days]@row * 8)

Does that make sense?

Cheers,

Genevieve

Paul Newcome

Are you able to provide a screenshot with manually entered data to show what you are trying to accomplish?

FlyFlip

Hi Paul. I took a screenshot in my post. Does that help or do you need more information?

Paul Newcome

I mean to remove the formulas and manually enter the numbers you want in those rows so we can visualize the intended outcome.

FlyFlip

Does this help?

Paul Newcome

Try this:

Time Off Days:

=IFERROR(NETWORKDAYS([Start Date]@row, [Ende Date]@row), "")

Time Off Hours Helper:

=IF([Time Off Days]@row = "", [Time Off Hours]@row, 0) + ([Time Off Days]@row * 8)

FlyFlip

Unfortunately neither work.

The Time off Days says "Unparseable" and Time Off Hours says "Circular Reference".

Is there anything I can do to help with the formulas?

Genevieve P.

Hi @FlyFlip

Can you post a picture of how you put in the first formula? It sounds like perhaps a comma or quote is in the wrong place.

For the second formula, this should be put in a new column, one that Paul called "Time Off Hours Helper", versus putting it in the Time Off Hours column. Does that make sense?

Paul Newcome

The time off days formula looks like I may have typed in one of your column names incorrectly. Make sure the column names are spelled how you have them in your sheet.

The second formula goes into a separate column as it looks like you are potentially going to have some sort of manual entry in the existing hours column.

FlyFlip

Thank you @Genevieve P. and @Paul Newcome. I got the Time Off Days to work.

For the Time Off Hours, it worked in a separate column but it does say #invalid when there is no data in the "time off days" column. Also, is there a way to have 1 "Time Off Hours" column instead of 2?

Genevieve P.

Hi @FlyFlip

Can you clarify how you're calculating the "Time Off Hours"? Is it that if there's no End Date selected, and there's only a Start Date, the time off is automatically "4 hours"?

If so, then yes, we can do it all in one column:

=IF([End Date]@row = "", 4, 0) + IF([Time Off Days]@row = "", 0, [Time Off Days]@row * 8)

Note that this is multiplying the number of days off by 8, assuming an 8 hour work day. If you mean to put this as a 4 hour work day, then you'd want to adjust the formula to multiply b 4 instead:

=IF([End Date]@row = "", 4, 0) + IF([Time Off Days]@row = "", 0, [Time Off Days]@row * 4)

However if the Time Off Hours will vary each day (e.g. someone may put in a Start Date but only have 2 hours off that day) there's no way for the formula to tell this. You would need the "Time Off Hours" column to be manually input, and then you can use the Time Off Hours Help column to create your calculation.

For example:

=IF([Time Off Days]@row = "", [Time Off Hours]@row, 0) + IF([Time Off Days]@row = "", 0, [Time Off Days]@row * 8)

Does that make sense?

Cheers,

Genevieve

FlyFlip

@Genevieve P. The 2nd option worked perfectly! Thank you for your detailed help!