Solved: Date formula

[austenr]

Trying to get this to work but when I enter the date as 7/1/1996 in D9 it returns false. Not the words.

=IF(D9<=7/1/1996,"Colgate",IF(D9<=7/2/1996,"Hills"))

[Sir Babydum GBE]

Trying to get this to work but when I enter the date as 7/1/1996 in D9 it returns false. Not the words.

=IF(D9<=7/1/1996,"Colgate",IF(D9<=7/2/1996,"Hills"))

Hi, try =IF(D9<=DATE(1996,1,7),"Colgate",IF(D9<=DATE(1996,2,7),"Hills"))

[Sir Babydum GBE]

Trying to get this to work but when I enter the date as 7/1/1996 in D9 it returns false. Not the words.

=IF(D9<=7/1/1996,"Colgate",IF(D9<=7/2/1996,"Hills"))

Or you could reference cells that contain your key dates instead - then you can enter the dates in those cells in whatever format you want as long as Excel recognises it as a date - but I think in your formula excel was treating it as a sum (7/1/1996=0.0035) so that's where the problem was

[Sir Babydum GBE]

Or... you could find out Excels serial number for the date in question and use that in the formula instead. E.g 07/01/1996 is 35071, and 07/02/1996 is 35102.

So your formula would be

=If(D9<=35071,"Colgate",If(D9<=35102,"Hills"))

You can find out the serial number for a given date by entering the date (recognisable to Excel) in a cell, then changing the format to "Number"

Ok - that's 3 answers - sorry, I'm not very good at this!

[austenr]

thanks more is good.

[austenr]

hmm.. none of those seem to work

[GTO]

Hi Austin,

There must be something shorter, but this seemed to work:


=IF(DATEVALUE(TEXT(D9,"m/d/yyyy"))<=DATEVALUE("7/1/1996"),"Colgate", _
IF(DATEVALUE(TEXT(D9,"m/d/yyyy"))=DATEVALUE("7/2/1996"),"Hills","Something Else"))

Please note that I used the underscore just to show a continuation, as the formula was long enough to "stretch" the page when I posted...

[austenr]

Thanks that got it.

[Bob Phillips]

This is shorter

=IF(D9<=--"1996-07-01","Colgate",IF(D9<=--"1996-07-02","Hills","some other value"))

[GTO]

This is shorter

=IF(D9<=--"1996-07-01","Colgate",IF(D9<=--"1996-07-02","Hills","some other value"))

(hand raised, a meek voice from the back of the classroom...)

"Err, Ummm, could you explain that?"

Hi Bob :hi:

As (or possibly if) I understand what I did was to force excel to grab the text and get the serial from that. While I knew, or leastwise greatly suspected, that it wasn't optimum - as is oft the case... Wow.

In your formula, what is excel reading from D9, the long? If so (or if not), what is it reading with using the double unarys? Does the string depend upon local settings?

Thank you so much,

Mark

[Bob Phillips]

In your formula, what is excel reading from D9, the long?

Yes, dates are always long and when you read a cell in Excel you read its value.


If so (or if not), what is it reading with using the double unarys?

The double unary (singular not plural, double means two of a single item!) is being used to coerce a string value to a number, in this case a string date to a long as the comparison cell contains. My reason for this is to make the formula more readable.


Does the string depend upon local settings?

No, because I used a reverse format date, as per the ISO standard, it is totally locale independent.

[GTO]

Yes, dates are always long and when you read a cell in Excel you read its value.

Thank you much :-)


(singular not plural, double means two of a single item!) Uhm, Ouch! Sorry, I remain a bit 'hillbilly/cowboy' and occasionally one would not know that English is my native tongue...


The double unary (singular not plural, double means two of a single item!) is being used to coerce a string value to a number, in this case a string date to a long as the comparison cell contains. My reason for this is to make the formula more readable.

This I will play with later (at present, it is well past my current 'Dracula' sleeping hours) to hopefully grasp, as I thought that (maybe SUMPRODUCT only?) we were getting a zero or one. Hopefully a few trys (and/or searching) and I become clearer in understanding.


...as per the ISO standard, it is totally locale independent.

ACK! I'm toast at that one... more searching for the blonde guy, but good to learn :-) Thanks again.

I'm sure you already 'got' this, but possibly for Austen:

From post#4 (If I am counting posts correctly - why I never think to look before flipping pages is a mystery), I believe I know why SirBabydum GBE's did not work for you. We are thinking that 7/1/1996 is July 1, but 35071 is January 7, 1996.

=IF(D9<=35247,"Colgate",IF(D9<=35248,"Hills"))

works for July 1 or 2 :-)

A good day to all, off to bed, lest I sleep through the alarm (again...).

Mark

[Bob Phillips]

From post#4 (If I am counting posts correctly - why I never think to look before flipping pages is a mystery), I believe I know why SirBabydum GBE's did not work for you. We are thinking that 7/1/1996 is July 1, but 35071 is January 7, 1996.

That's exactly right. We use a dd/mm/yy date format in the home of the English language, so Sir BD read that date as 7th Jan.

[GTO]

...in the home of the English language,...

I almost missed that. I SOOO have to hit the rack, but must relay... Yer killin' me/wiping tears laughing. I truly hope I get to buy you a beer one day.