Formula Help

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?

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.

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

All comments

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

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

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

Does this help?

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)

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?

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?

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.

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?

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)

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