Excel, check if date is older then current month

Get help on programming - C++, Java, Delphi, etc.
Post Reply
Mclaren
Registered User
Posts: 497
Joined: 30 Apr 2007, 02:00
Location: c:\program files\temp
Contact:

Excel, check if date is older then current month

Post 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.
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Excel, check if date is older then current month

Post 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.
Image
User avatar
rustypup
Registered User
Posts: 8872
Joined: 13 Dec 2004, 02:00
Location: nullus pixius demonica
Contact:

Re: Excel, check if date is older then current month

Post 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"))
Most people would sooner die than think; in fact, they do so - Bertrand Russel
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Excel, check if date is older then current month

Post by Tribble »

Very true puppi - I assumed he would extend the box to add the if for Feb. Yours is more elegant.
Image
Mclaren
Registered User
Posts: 497
Joined: 30 Apr 2007, 02:00
Location: c:\program files\temp
Contact:

Re: Excel, check if date is older then current month

Post by Mclaren »

Extend....Cough...Splutter...Cough....Cough.....

When it comes to excel VB i am clueless.

Tahnks guys gonna test the code now.
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Excel, check if date is older then current month

Post by Tribble »

I'm that is not VBa. That is just a formula
Image
Mclaren
Registered User
Posts: 497
Joined: 30 Apr 2007, 02:00
Location: c:\program files\temp
Contact:

Re: Excel, check if date is older then current month

Post 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.
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Excel, check if date is older then current month

Post by Tribble »

Why did you need it in VBA when it works easily in the cell?
Image
Mclaren
Registered User
Posts: 497
Joined: 30 Apr 2007, 02:00
Location: c:\program files\temp
Contact:

Re: Excel, check if date is older then current month

Post 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.
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Excel, check if date is older then current month

Post by Tribble »

Ah ha. I see.
Image
User avatar
rustypup
Registered User
Posts: 8872
Joined: 13 Dec 2004, 02:00
Location: nullus pixius demonica
Contact:

Re: Excel, check if date is older then current month

Post by rustypup »

if you're doing this via VBA you have access to DateDiff and far better precision... :/

Code: Select all

DateDiff("m", dateA, now())
a negative result indicates a future date
a positive result a prior date
a zero result is the current month...
Most people would sooner die than think; in fact, they do so - Bertrand Russel
Mclaren
Registered User
Posts: 497
Joined: 30 Apr 2007, 02:00
Location: c:\program files\temp
Contact:

Re: Excel, check if date is older then current month

Post 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
User avatar
rustypup
Registered User
Posts: 8872
Joined: 13 Dec 2004, 02:00
Location: nullus pixius demonica
Contact:

Re: Excel, check if date is older then current month

Post 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...
Most people would sooner die than think; in fact, they do so - Bertrand Russel
Mclaren
Registered User
Posts: 497
Joined: 30 Apr 2007, 02:00
Location: c:\program files\temp
Contact:

Re: Excel, check if date is older then current month

Post 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""))"
Mclaren
Registered User
Posts: 497
Joined: 30 Apr 2007, 02:00
Location: c:\program files\temp
Contact:

Re: Excel, check if date is older then current month

Post 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
Mclaren
Registered User
Posts: 497
Joined: 30 Apr 2007, 02:00
Location: c:\program files\temp
Contact:

Re: Excel, check if date is older then current month

Post 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
User avatar
rustypup
Registered User
Posts: 8872
Joined: 13 Dec 2004, 02:00
Location: nullus pixius demonica
Contact:

Re: Excel, check if date is older then current month

Post 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.
Most people would sooner die than think; in fact, they do so - Bertrand Russel
Post Reply