Excel, check if date is older then current month
-
- 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
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.
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.
- 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
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.
=IF(DAYS360(A1,$L$1)<30,IF(MONTH(A1)=MONTH($L$1),"Current","Future Dated"),"Old")
That should give you what you want.
- 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
:/... 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...
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
- 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
Very true puppi - I assumed he would extend the box to add the if for Feb. Yours is more elegant.
-
- 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
Extend....Cough...Splutter...Cough....Cough.....
When it comes to excel VB i am clueless.
Tahnks guys gonna test the code now.
When it comes to excel VB i am clueless.
Tahnks guys gonna test the code now.
- 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
I'm that is not VBa. That is just a formula
-
- 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
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.
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.
- 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
Why did you need it in VBA when it works easily in the cell?
-
- 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
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.
- 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
Ah ha. I see.
- 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
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...
Code: Select all
DateDiff("m", dateA, now())
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
-
- 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
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
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
- 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
Yes... the DateDiff function ... the "m" is the datepart you're interested in, (months). Therefore :
you can use a Select Case statement to process the result and flag as necessary...
Important to note that DateDiff will throw a wobbly if the value provided is an invalid timestamp... so you will need to trap this...
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
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
Most people would sooner die than think; in fact, they do so - Bertrand Russel
-
- 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
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""))"
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""))"
-
- 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
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
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
-
- 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
Don't answerjust yet, going to go research the function first.
If i still don't come right, i will let you know
If i still don't come right, i will let you know
- 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
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
DateSerial will construct a valid timestamp from the arguments provided.
Most people would sooner die than think; in fact, they do so - Bertrand Russel