Date issue in excel

[N1k100]

I do the same spreadsheet every quarter, have done for a few years and always had a date calculating formula, have used the same one for a long time.

So the user enters a date in B19 and I want it to automatically calculate that date plus nine days in C19
I have always entered this formula in C19 and had no problems. =IF(AND(B19>39000,B19<300000),B19+9,"")

Now, when the user enters the date nothing shows up in C19. If I click on the cell and press enter it "may" show but not all the way down the column.

I know I could use B19+9 which would be easy but it shows a date in C19 before a date is entered in B19 and I didnt want that.

Any suggestions or can someone see an error in formula but I copied it from past spreadsheet! I just want a formula that wont show anything until a date is entered in B19. Could it be that the computer was upgraded?

Thanks
Nikky.

[Tony Valko]

If the cell appears to be blank then that means this portion of the formula evaluated to FALSE:

AND(B19>39000,B19<300000)

If B19 contains a TEXT string that looks like a date then:

B19>39000 = TRUE
B19<300000 = FALSE

What result do you get from this formula:

=IF(ISNUMBER(B19),IF(AND(B19>39000,B19<300000),B19+9,""),"Not A True Excel Date")

[N1k100]

Well that was the strangest! I inserted your formula and I got "not a true excel date" but I re clicked on the cell and hit enter and it gave me the date I wanted. I then dragged the formula down the column but although clicking on those cells it shows that formula inside it, it gives me the first date all the way down. I must be totally missing something. I have all cells formatted to date.

[Tony Valko]

Sounds like you have calculation set to manual.

Got the Formulas tab>Calculation Options>Automatic

[N1k100]

Ok, I think I have it. The computer default is English (US), I was trying English (Canadian) but after I changed it to date format US, and put month first, it works! Oh my what a frustrating day! If I wanted to use the Canadian do I have to change the default?

All fixed, and working on main sheet too, thank you so much!

[Tony Valko]

If I wanted to use the Canadian do I have to change the default?

I didn't know there was a difference between the U.S. date format and the Canadian date format. If you want to set it to a Canadian format then you have to do that through the operating system.

All fixed, and working on main sheet too, thank you so much!

Good deal!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

[N1k100]

I just did a reply, must be getting frustrated

Thank you, yes the calculation was on manual which I have changed. Also noticed that when setting the date format, the location was different each time I went in even though its set to English (US). So I have ensured that all are set and they are formatted to 14 Mar 01, but if I type say 28/4/14 it stays that way. Is there any other settings that could be causing my issue?

[martindwilson]

The default date format used by Microsoft Windows for English Canada for all-numeric dates (short-dates) is DD/MM/YYYY