Workday function combined with If function help

[ttanika]

I have been working on this for a while and I can not figure it out.

G8= Student entry date
H8 = # of days in attendance
I8 = =IF(H8<=30,P8-H8,"Ineligible")
column I = the date students are eligible for 30 day release

P8 = =IF(G8>=1,G8+75,"")

column P = the date students are eligible for 75 day release

I need P8 to exclude weekends and holidays, so the 75 day release date will not include weekends and holidays. I tried to use the workday function. Can't figure it out. I have a list of the holidays on another sheet called Holidays.

 

[barry houdini]

You could use a regular worksheet formula with WORKDAY function like this

=IF(G8>=1,WORKDAY(G8,75,Holidays),"")

 

[ttanika]

You could use a regular worksheet formula with WORKDAY function like this

=IF(G8>=1,WORKDAY(G8,75,Holidays),"")

Thanks for your assistance! Yesterday, I did end up figuring out the workday function like the one you suggested. I ran into problems with other formulas in another cell. Is there a way to have

G8 = # of days in attendance (only count/include workdays) so that this formula =IF(H8<=30,P8-H8,"Ineligible") will work in cell I8 and return the 30 day release date of 3/1/2013 (weekends and 2 holidays are excluded)?

 

[ttanika]

=IF(H8<=30,P8-H8,"Ineligible") is the formula used in cell I8 in order to calculate the 30 day release date. Can I use the code above and change the formula in a way so I will have the same results as =IF(H8<=30,P8-H8,"Ineligible") but excluding the weekends and holidays?

 

[ttanika]

How would I do this to alter this formula in cell (I8)

=IF(H8<=30,P8-H8,"Ineligible")

to determine the 30th day. (now that I think about it that formula would not give me a 30 day release it give me 45th day, so this is an incorrect formula.

Should I just do an entirely new formula for cell I8 to calculate the 30th day based on how many day the student has attended school (cell H8)? If so, What function or code can i use that will produce a 30th day based on what's entered in cell H8?

 

[ttanika]

Your formula is adding 75 days to the start day, making the end day the 76th day including the start day. If you change 75 to 74 in the code make sure your change it it both places (=IF(G8>=1,G8+74+NumberOfExtraDays(G8, G8+74),"")).

If you want a formula to determine the 30th day (assuming your start day is day one) use the same code as the 75 days but add 29 (or 30 if the start date doesn't count) instead of 74/75

=IF(G8>=1,G8+29+NumberOfExtraDays(G8, G8+29),"")

I tried this in cell I8

=IF(H8<=30,WORKDAY(G8,30,Holidays),"Ineligible")

but this gives me 3/4/2013 instead of 3/1/2013. How can I fix this?

If I use the workday function in I8 it will calculate the 30th date no matter what number (1-30) is entered in cell H8. I really need the date to adjust depending on the # of days the student has attended school (1-30) anything after 30 days the student is ineligible for 30th release.

I hope i'm not confusing you I really appreciate your help.

 

[Rosen]

Are you looking for a formula that works like this?

=IF(H8<=30,WORKDAY(P8,-H8-15,Holidays),"Ineligible")

 

[ttanika]

Are you looking for a formula that works like this?

=IF(H8<=30,WORKDAY(P8,-H8-15,Holidays),"Ineligible")

Yes!!! It worked! Thank you so much! I have another favor to ask. This is the formula cell M8

=IF(L8<=0,"Eligible",IF(L8="a",P8+30,IF(L8="b",P8+45,IF(L8="c",P8+60," "))))

I need to do the same thing with this cell (exclude Weekends and Holidays). If L8="a",P8+30(the 30 days should not include weekends and holidays) and the same for P8+45 and P8+60. What would be the formula?

 

[Rosen]

Try this:

=IF(L8<=0,"Eligible",IF(L8="a",WORKDAY(P8,P8+29,Holidays),IF(L8="b",WORKDAY(P8,P8+44,Holidays),IF(L8="c",WORKDAY(P8,P8+59,Holidays)," "))))

Note that I am assuming that the Day in P8 is day one in your 30, 45 and 60 days after. If it is day 0 then you will need to change the bolded text to 30, 45 and 60 respectively.

Hope that helps!

 

[ttanika]

Try this:

=IF(L8<=0,"Eligible",IF(L8="a",WORKDAY(P8,P8+29,Holidays),IF(L8="b",WORKDAY(P8,P8+44,Holidays),IF(L8="c",WORKDAY(P8,P8+59,Holidays)," "))))

Note that I am assuming that the Day in P8 is day one in your 30, 45 and 60 days after. If it is day 0 then you will need to change the bolded text to 30, 45 and 60 respectively.

Hope that helps!

I tried this and I get 9/24/2172 as the date (if L8 = a). I should get 9/13/2013 as the date. I did adjust the holiday range to include the summer as holidays and the holidays into the next school year, but I still get the wrong date.

 

[ttanika]

I tried this and I get 9/24/2172 as the date (if L8 = a). I should get 9/13/2013 as the date. I did adjust the holiday range to include the summer as holidays and the holidays into the next school year, but I still get the wrong date.

Any other ideas?