Thread: Revising day of the week automatically

Hi -


I simply don't have the skills yet to handle this issue.

I am trying to push some original due dates to specific days of the month depending on the range in which the original due date falls.

The orignial due date gets revised during processing to one of the 3 following days of the month: the 5th, the 20th, or the 25th.

Right now it is being done MANUALLY but there are 1,000s of entries that must be processed, and i'm pretty sure it can be done automatically.

The formula should only look to the day of the date. But the raw data will display the month and year and the resulting data should display the month and year.

So if the DUE DATE is >1 but < or equal to 5, the REVISED DATE should return the 5th.
If the DUE DATE is >5 but < or equal to 20, the REVISED DATE should return the 20th.
If the DUE DATE is >20 but < or equal to 25, the REVISED DATE should return the 25th.
If the DUE DATE is >25, it should return a BLANK.

Here is an example of the data:

DUE DATE REVISED DATE
9/4/2011 FORMULA WOULD RETURN 9/5/2011
9/12/2011 FORMULA WOULD RETURN 9/20/2011
9/22/2011 FORMULA WOULD RETURN 9/25/2011

Let me know if i left out any info.

Thanks everyone!

-BLNYC

Welcome to the forum.

Supposing your due date is in A2, this formula should give the correct, revised date:

=IF(DAY(A2)>25,"",DATE(YEAR(A2),MONTH(A2),LOOKUP(DAY(A2),{1,6,21},{5,20,25})))