You are viewing limited content. For full access, please sign in.

Question

Question

combine 3 if formulas with dates

Forms
Updated March 10, 2022
asked on March 10, 2022

Is it possible to combine the following IF formulas into one big one. These will all be from a table on the same row attempting to fill out my "Flex" column. 

The rules I want to capture are:

1)if Type = Flex, then Flex = -7

2) if Type = regular and Date = Mon-Thurs, then Flex = 0.5

3) if Type = regular and Date = Fri-Sun, then Flex = 0/blank

 

I've already managed to combine differentiate between having -7 and 0.5 based on the type selected, but I'm not sure how to get the date one in the mix. 

This is my formula for the two variables above:

=IF(INDEX(Hours_Worked.TypeDay, ROW())=Flex, "-7", IF(INDEX(Hours_Worked.TypeDay, ROW())=Regular, "0.5", "0"))

0 0

Answer

SELECTED ANSWER
replied on March 10, 2022

Hi Cathrine,

The day of week can be calculated by WEEKDAY formula. (e.g. =WEEKDAY(Date,2) returns 5 if Date is 3/11/2022).

So the desired formula would be

=IF(INDEX(Hours_Worked.TypeDay, ROW())="Flex", "-7", IF(AND(INDEX(Hours_Worked.TypeDay, ROW())="Regular", WEEKDAY(INDEX(Hours_Worked.Date, ROW()),2)<5), "0.5", "0"))

1 0

Replies

You are not allowed to reply in this post.
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...