0

How do I set a formula or VBA to get around this?

If Date Listed in Column A + (7 Days) Exceed 5th of Upcoming Month then Do Something.

Its like 5th of Each Month is Deadline.
If Column A = 30th March 2015 +(7days)left = 6th April 2015 then Column B = "HolyCrap" Else "You're Safe"

I would really appreciate some help on this.

ZygD
  • 8,011
  • 21
  • 49
  • 67
Manpreet Juneja
  • 35
  • 1
  • 7

2 Answers2

0

Let's try this formula:

=IF(AND(DAY(A1+7)>5,DAY(A1+7)<13),"screwed","safe")

The date in A1 could be in either Date or Number format. You would get the correct Number format after pressing Ctrl+;.

Please test it and let me know if for some date it does not return the correct result.

ZygD
  • 8,011
  • 21
  • 49
  • 67
  • Nah, 2 Issues.. **1.** Its giving me #Value Error. (Converted date to number and manually replaced it with A1 in the formula, then it works) **2.** I have 3rd Sep 2015 in A1...(+7 days) = 10th Sep 2015 which crosses 5th Sep Deadline. It still says "Safe". :/ – Manpreet Juneja Apr 03 '15 at 22:30
  • **1.** Your dates should be always be of a *date* format (which is actually a number)! no formula will work on dates which are stored as *text*. **2.** You said *upcoming month*. So, 10th Sep is definitely bigger than the fifth of the upcoming month (5th May). therefore you're safe. Can you please clarify the question? – ZygD Apr 03 '15 at 22:40
  • It was under the date format, had to change it to numeric format for it to work. **2.** Sorry about that. This is the tricky Part..5th of Each Month is the Deadline. Today wasn't gonna cut it as formula is not in relation to Current date. Its like the date A1 plus 7 days (week) hits the 5th of arriving month from that date then "no" else "yes". Example: 30 Oct + 7 days = 6th Nov (Crosses Nov 5 Deadline) Hence "No" 30 Sep + 7 days = 5th Oct (Doesn't Cross Oct 5 Deadline) Hence "Yes" – Manpreet Juneja Apr 03 '15 at 23:52
  • Classy code!! and a Big Thanks for understanding from all the jumbled up info u received :) – Manpreet Juneja Apr 07 '15 at 13:06
  • Happy to help :) Good luck! – ZygD Apr 07 '15 at 19:04
0

You can use 3 different functions to achieve this in Excel: IF, TODAY and DAY360.

You need a column to calculate the present date. Use TODAY function for that. You need a column to calculate the number of days difference between the two dates (that is, the present date and your target date). Use the DAYS360 function for that.

Then on the cell / Column you require to display whatever you want it to display use the IF function. It will help determine if the current date is over or under your specified value in days, and return whatever value you want displayed; overdue, expired, Yes, No etc. Example:

=IF(P2>30,"Expired","Active")
RamenChef
  • 5,533
  • 11
  • 28
  • 39
Femzie
  • 1