I have an issue with trying to create 3 date ranges automatically within excel. (cannot use the analysis pack functions due restrictions for other users of this workbook)
The date ranges I require are Mon-Sun, Fri-Thurs, Sat-Fri
I have already managed to get Mon-Sun working (See below)
Basically there are 2 columns
I have 2 defined ranges to called WDF (Weekly date from) and WDT (Weekly Date to)
A1 =WDT
B1 =IF(A1="","",IF(INT(A1)-WEEKDAY(A1,3)+6>WDT,WDT,INT(A1)-WEEKDAY(A1,3)+6))
This formula is filled down as far as I required (8 rows but no relevant)
A2 =IF(B1="","",IF(B1+1>WDT,"",B1+1))
What the above does is ensure that no matter what date range the user chooses I will have a weekly dates from 1st date (WDF) to the Sunday within the same week and them Mon-Sun for each respective week until the WDT is reached anything else will blank the cells.
My problem has arisen when trying to amend this formula to do have the same result Fri-Thur and Sat-Fri
Any help would be appreicated.
Thanks
Bookmarks