Excel "IF" Function ?

  jimbo2287 21:45 01 Feb 2007
Locked

My problem, on an excel spread sheet containing variuos financial information and reference numbers. I have a column with dates ranging from 01/01/98 right up to current date. I have a blank column in which I want to insert "(A) Period pre 2000" for those dates upto 31/12/1999; "(b)Period 2000" for those ranging between 01/01/2000 and 31/12/2000; "(C) Period 2001" for year 2001 etc. The purpose of the message I want to insert is so that they will be listed in a Aged Report on a pivot table.
At present I sort the dates, and copy paste the messages on the spread sheet. This is rather long winded as the spread sheet is several thousand lines long. I think the "If Function" is what I need, but I can't find anything allowing me to work with dates. Help please.

  chocolate cake 22:09 01 Feb 2007

The date in excel is actually a number converted to a date. The dates start 1/1/1900.

From memory, I don't have excel at home now to test this, you can run the =IF statement based on the number represented by the date.

=IF(A1<"date number",Period pre2000,IF(- and so on.

Try opening a separate sheet and typing in a date say 1/1/2000 and then highlight/format and choose number. That will give you your number for pre 2000.

  chocolate cake 22:21 01 Feb 2007

=IF(A1<36526,"Pre 2000",IF(A1<36891,"Period 2000",IF(A1<36892,"Period 2001",""))) etc.

  chocolate cake 22:22 01 Feb 2007

With a few changes to the numbers it should work.

  chocolate cake 22:56 01 Feb 2007

Forgot about empty cells. This should work better. I'm sure that ther are prettier formulas that others could advise you of.

=IF(A1="","",IF(A1<36526,"Pre 2000",IF(A1<36892,"Period 2000",IF(A1<37257,"Period 2001",IF(A1<37622,"Period 2002","")))))

  johnnyrocker 23:28 01 Feb 2007

vog where are you:)


johnny

  chocolate cake 23:32 01 Feb 2007

Just what I was wondering.

  p;3 23:40 01 Feb 2007

he (vog)may catch up with you tomorrow; he's been off line :))

  VoG II 09:10 02 Feb 2007

=IF(YEAR(A1)<2000,"Period pre 2000","Period "&YEAR(A1))

  VoG II 09:41 02 Feb 2007

Accounting for blanks:

=IF(ISBLANK(A1),"",IF(YEAR(A1)<2000,"Period pre 2000","Period "&YEAR(A1)))

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

WPA2 Hack Latest News: How Secure is your Wi-Fi?

Photoshop CC 2018 released with new Curvature Pen and better brush tools

Best kids apps for iPhone & iPad

Comment utiliser Twitter ?