Check specific date

yippie_ky_yay

Board Regular
Joined
Jun 25, 2002
Messages
63
Hello all,

This seems simple but I can't seem to get the result. All I need to do is check a specific column (in this case, J) for the date, and if that date is greater then April 30, 2002 THEN show whatever is in the corresponding row in column K (otherwise show nothing).

I know I must be close with:
=IF(J7>4/30/2002,K7,"")

I guess my problem is comparing dates!

Thanks in advance,

-Sean
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

maxflia10

Well-known Member
Joined
May 20, 2002
Messages
890
Sean,

The formula works for me. What kind of error message are you getting?
 

ThePencilQueen

Board Regular
Joined
Jun 26, 2002
Messages
109
Try using 37376 instead of the date format. (If you format your data as a number this is what 30th Apr 2002 is equal to (the number of days since 1st Jan 1900))
 

yippie_ky_yay

Board Regular
Joined
Jun 25, 2002
Messages
63
Thank you both for answering my post! Using the numeric value (37376) as ThePencilQueen suggested did the trick and everything works great!

Thanks again,
-Sean
 

maxflia10

Well-known Member
Joined
May 20, 2002
Messages
890

ADVERTISEMENT

Sean

What date system have you checked off in Tools/Options?
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
If you'll use the following your formula will be more "readable"...

=IF(J7>"4/30/2002"+0,K7,"")
 

yippie_ky_yay

Board Regular
Joined
Jun 25, 2002
Messages
63
Neat trick Mark - thanks!

maxflia10 - in case you're still interested - I'm using '97 and under tools options the only reference to a date system that I can see is a check box for "1904 date system" which I currently do not have selected (nor understand - I'll look it up later when I have more time).

Thanks again everyone!
-Sean
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
Not only is...

=IF(J7>"4/30/2002"+0,K7,"")

...more readable, but it also makes your workbook more portable because the datevalue above is calculated "on the fly". The 1904 date system is the default on Macintoshes. Windows defaults to 1900 (unchecked). When using the 1904 date system January 1, 1904 is assigned the datevalue, 1. The datevalue, 1, is ordinarily assigned to January 1, 1900 on a Windows PC. So, if you hard code a datevalue in a formula and move that workbook to a system using another date system your formulas will be off by 4 years. For more on date systems see the Excel Help topic for "Tips on entering dates and times" and "How Microsoft Excel stores dates and times".
This message was edited by Mark W. on 2002-10-30 11:18
 

Watch MrExcel Video

Forum statistics

Threads
1,122,331
Messages
5,595,541
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top