Calculate Date based on 3 factors
Posted on 2015-02-06
I have a challenge that I have just not been able to resolve.
I have a spreadsheet with 3 fields. 1---Frequency (Dly, Sat, Sun) 2---DOW (Day of the week) 3--Original Date.
What I need to do is to have a formula that would do this: If DOW is a weekday and the freq is "dly" keep the original date, if DOW is a Sat and freq is a Sat keep original date, if DOW is a Sun and Freq is a Sun keep the original date but if the DOW is a DOW is a weekday but the Freq is a Sat or Sun then give me the Sat or Sun date for that same week. If DOW is a Sat or Sun but the Freq is DLY then give me the "Friday" of that week. I have been using a formula that I was hoping to modify to meet these rules but I can't get it to work. =IF(C2="DLY",IF(G2="Sat",F2-1,IF(G2="Sun",F2,F2)),IF(C2="SAT",F2+WEEKDAY(F2+1)+1,F2-WEEKDAY(F2+1)+2))
Any help would be greatly appreciated.