Page 1 of 1
Excel, check if date is older then current month
Posted: 09 Jan 2012, 21:31
by Mclaren
I am trying to eliminate repetetive formating of worksheets and have come accross a problem where i need to determine when a product expires:
I have found many example of wether a date is older than today, but i need to fin out if cell is older than current month.
if cell A1 has date : 2011/12/31, because current month is Jan, i need cell B1 to report "old"
if cell A1 has date : 2012/01/31, because current month is Jan, i need cell B1 to report "Current"
if cell A1 has date : 2012/02/31, because current month is Jan, i need cell B1 to report "Future Dated"
Any ideas.
Re: Excel, check if date is older then current month
Posted: 10 Jan 2012, 07:11
by Tribble
That is easy - use the Days360 function together with the If function. Assume today's date (=today()) is located in L1. The date you are looking for is in A1.
=IF(DAYS360(A1,$L$1)<30,IF(MONTH(A1)=MONTH($L$1),"Current","Future Dated"),"Old")
That should give you what you want.
Re: Excel, check if date is older then current month
Posted: 10 Jan 2012, 07:38
by rustypup
:/... February's max daycount is 29 in a leap...
days360 assumes 12 months of exactly 30 days each, (ie, 30x12==360)... so, not the required precision...
Code: Select all
=IF(AND(YEAR(<somecell>)=YEAR(NOW()),MONTH(<somecell>)=MONTH(NOW())),"Current",IF(AND(YEAR(<somecell>)>=YEAR(NOW()),MONTH(<somecell>)>MONTH(NOW())),"Future","Old"))
Re: Excel, check if date is older then current month
Posted: 10 Jan 2012, 07:57
by Tribble
Very true puppi - I assumed he would extend the box to add the if for Feb. Yours is more elegant.
Re: Excel, check if date is older then current month
Posted: 10 Jan 2012, 19:33
by Mclaren
Extend....Cough...Splutter...Cough....Cough.....
When it comes to excel VB i am clueless.
Tahnks guys gonna test the code now.
Re: Excel, check if date is older then current month
Posted: 10 Jan 2012, 21:02
by Tribble
I'm that is not VBa. That is just a formula
Re: Excel, check if date is older then current month
Posted: 10 Jan 2012, 21:25
by Mclaren
Vocab, English Language, thinking before typing....comes difficult to me....LOL
Anyways, I needed it for VB that why i said what i said, and a few tweaks and yes i got it working. what a mission.
Re: Excel, check if date is older then current month
Posted: 11 Jan 2012, 06:16
by Tribble
Why did you need it in VBA when it works easily in the cell?
Re: Excel, check if date is older then current month
Posted: 11 Jan 2012, 06:24
by Mclaren
I receive a spreadsheet that needs to be formatted every Dayan the same way. I used to check the values manually, So I have made a macro to do all of it in one go. This includes inserting formulas.
Re: Excel, check if date is older then current month
Posted: 11 Jan 2012, 06:37
by Tribble
Ah ha. I see.
Re: Excel, check if date is older then current month
Posted: 11 Jan 2012, 07:57
by rustypup
if you're doing this via VBA you have access to DateDiff and far better precision... :/
a negative result indicates a future date
a positive result a prior date
a zero result is the current month...
Re: Excel, check if date is older then current month
Posted: 11 Jan 2012, 22:14
by Mclaren
Thanks Rustypup,
Afet spending 3 hours last night tweaking the oringal formula. It still does not work properly, I will post my formula tomorrow, with an example of the errors.
After reading my original post, I noticed i did not state my requirements correctly.
My Aim is to: determine if a cell value is Older than current month, In the current month, or within 2 months of current month.
Is there an easy way to do this in VB ?
Reults must show,
Older than current month = Old
within current month + Current
With in 2 months of current month = Future
Any thing further in the future should show null
So, If current month = JAN
Anything in Dec or older must be marked as OLD
Anything in Jan must be marked CURRENT
Anything in Feb or March must be marked as FUTURE
Anything in Apr or thereafter must be marked as NULL
Re: Excel, check if date is older then current month
Posted: 12 Jan 2012, 08:07
by rustypup
Yes... the
DateDiff function ... the "m" is the datepart you're interested in, (months). Therefore :
Code: Select all
DateDiff("m",now,DateSerial(2011,12,7))== -1|Expired
DateDiff("m",now,DateSerial(2012,01,23))== 0 | Current
DateDiff("m",now,DateSerial(2012,02,19))== 1 | Future
DateDiff("m",now,DateSerial(2012,03,16))== 2 | Future
DateDiff("m",now,DateSerial(2012,04,2))== 3 | Null
you can use a
Select Case statement to process the result and flag as necessary...
Code: Select all
Select Case DateDiff("m",now,<SomeCellValue>)
Case 0 <do-something>
Case Is <0 <do-something>
Case 1 To 2 <do-something>
Else <do-something>
End Select
Important to note that DateDiff will throw a wobbly if the value provided is an invalid timestamp... so you will need to trap this...
Re: Excel, check if date is older then current month
Posted: 12 Jan 2012, 12:22
by Mclaren
Thanks Rustypup:
This is my current formula:
ActiveCell.Formula = "=IF(AND(YEAR(G2)=YEAR(NOW()),MONTH(G2)=MONTH(NOW())),""EXP - UPLIFT"",IF(AND(YEAR($G2)>=YEAR(NOW()),MONTH($G2)>MONTH(NOW())),IF(AND(YEAR($G2)>=YEAR(NOW()),MONTH($G2)>MONTH(NOW())+1),IF(AND(YEAR($G2)>=YEAR(NOW()),MONTH($G2)>MONTH(NOW())+2),"""",""UPLIFT - EXP""),""UPLIFT - EXP""),""EXP - UPLIFT""))"
Re: Excel, check if date is older then current month
Posted: 12 Jan 2012, 12:24
by Mclaren
Is there a reason why there is a date after the DateSerial ?
DateDiff("m",now,DateSerial(2011,12,7))== -1|Expired
DateDiff("m",now,DateSerial(2012,01,23))== 0 | Current
DateDiff("m",now,DateSerial(2012,02,19))== 1 | Future
DateDiff("m",now,DateSerial(2012,03,16))== 2 | Future
DateDiff("m",now,DateSerial(2012,04,2))== 3 | Null
Re: Excel, check if date is older then current month
Posted: 12 Jan 2012, 12:29
by Mclaren
Don't answerjust yet, going to go research the function first.
If i still don't come right, i will let you know
Re: Excel, check if date is older then current month
Posted: 12 Jan 2012, 12:34
by rustypup
Code: Select all
Select Case DateDiff("m",now,Range("G2").Value)
Case Is <0 ActiveCell.Value="EXP - UPLIFT"
Case 1 To 2 ActiveCell.Value="UPLIFT - EXP"
Else ActiveCell.Value=""
End Select
<warning: VB is not in my toolset so the syntax may be botched but IIRC Range is the preferred option... >
DateSerial will construct a valid timestamp from the arguments provided.