Formula Issue

Adriane Price

I am attempting to calculate how many hours total worked from columns "Requested Start Date", "Requested End Date" and "Requested Hours".

This way I can figure out if hours are under 4 (hrs.), if it is a weekend and what weekend day it is for example "Saturday" or "Sunday".

Is there an easier way and I have been looking at this too long now?

Genevieve P.

Hi @Adriane Price

It looks like you're attempting to divide an entire column range ([Requested Hours]@row:[Requested Hours]@row) instead of just the one cell in the current row ([Requested Hours]@row)

Try this:

=IF([Time of Day Reqs]@row = "After Hours", "N/A", IF([Requested End Date]@row - [Requested Start Date]@row < 1, [Requested Hours]@row / ([Requested End Date]@row - [Requested Start Date]@row + 1)))

I also added parentheses around what you want to divide by.

Let me know if this works for you, now!

Cheers,

Genevieve

Genevieve P.

Hi @Adriane Price

It looks like you're attempting to divide an entire column range ([Requested Hours]@row:[Requested Hours]@row) instead of just the one cell in the current row ([Requested Hours]@row)

Try this:

=IF([Time of Day Reqs]@row = "After Hours", "N/A", IF([Requested End Date]@row - [Requested Start Date]@row < 1, [Requested Hours]@row / ([Requested End Date]@row - [Requested Start Date]@row + 1)))

I also added parentheses around what you want to divide by.

Let me know if this works for you, now!

Cheers,

Genevieve

Adriane Price

@Genevieve P. - I see my extra add-in and understand that was a cell by cell instead of the row. Thank you, the formula you provided cleared up my "Invalid Operation". The only bit I am missing is the actual hours from the Requested Start Date and Requested End Date so Hours Worked technically should be showing 32 is there more to the current formula that I should be adding in or I am missing?

I have attempted a few other variables in the formula provided

BUT when I did I received this

Genevieve P.

Hi @Adriane Price

Can you detail exactly what you want the formula to return?

Right now it's taking the Requested Hours and dividing it by the Number Of Days (Start to End).

Is that what you're looking for, to see how many hours per-day? Are you wanting to include Weekend Dates into the equation (ex. if the Start is on Friday and End is on Monday, is that 2 or 4?)

Adriane Price

I am looking to see how many hours per day based on an 8 hour day during the weekday.

I have a weekend column(s) separately that I have not started working on just yet, thinking they may be similar to the weekday.

Genevieve P.

Hi @Adriane Price

If a user provides you with the requested hours (ex. 40) and you're looking to see how many days there are, we would use the hours / 8 to retrieve how many days (40h/8h = 5d). Then we can use a WORKDAY formula to add that number to the Start Date and return the End Date (Start Date + 5d).

However, in your instance it looks like they're already providing you with both the Start and End date, as well as the hours requested. From this we could divide the hours by the days to see, spread out over the days requested, how many hours per-day is needed. But this may be either under or over 8 hours. How are you wanting to add the 8-hours into this equation?