dates not working on one spreadsheet??

[ChicagoGuy]

I have an odd problem that has been plaguing me on a particular spreadsheet. I am hoping there is some funky setting somewhere that fix this that I have somehow screwed up.

I have continual problems with date functions. Its as if the sheet will not recognize a data as a date. Its truly bizarre and it has come up in multiple attempts at different functions using dates. Today glitch is so simple but it will not work. Here is the funny thing. It WILL work if I open a NEW spreadsheet on my office computer. But it WILL NOT work on my office computer on my current spreadsheet.

So the formula works but not the spreadsheet. Again the one I am showing you below is simple but i have tried others in the process of building this and they have also acted very odd. Forexample I cannot use Today() to calculate anything.

OK here is what I am trying to do. The first table shows what a new spreadsheet gets and the second table show what my current spreadsheet gets. Help me Obi Wan


Using the following formula =MIN(H32:H38) I get 3/21/17 in the new spreadsheet

8/1/2017

3/21/17

4/11/2017

4/18/2017

4/11/2017

<tbody>
</tbody>

Here is the result using the same formula on my current spreadsheet
Using the following formula =MIN(H32:H38) I get 1/0/1900

8/1/2017

3/21/17

4/11/2017

4/18/2017

4/11/2017

<tbody>
</tbody>

Both are properly formatted as dates. You clearly see the current spreadsheet is not only failing its giving me a date that isnt possible. Its driving me nuts and I cannot figure what is going on. How could the dates effectively be turned off? How could I even get a date that have 0 in it.

It probably doesnt matter since both the working one and the non working one are both same but the excel version is excel20163 (15.0.4859.1000) MSO (15.0.4919.1002) 32but

 

[pvr928]

Hi ChicagoGuy

Just a thought - check the range you are using in the MIN() formulae - the range you have stated in your question has 7 rows, whereas in your sample (and I appreciate you are suggesting it is only indicative of your data), there are only 5 rows.

1/0/1900 is the result of MIN(0) - Excel's first date.

As for you saying you cannot get TODAY() to calculate anything - I'm not sure what that means.

Cheers

pvr928

 

[JoeMo]

Maybe on the one you are having trouble with, your dates are text rather than numbers. What does =ISNUMBER(H32) return?

 

[SpillerBD]

IF the column is only suppose to have dates (and Header Text) select the column and perform the Text to Columns menu function. Select the correct date formatting for step 3, NOT general.

 

[ChicagoGuy]

Hey thank. Good catch on that but yes I just rewrote the table and left off the bottom two dates by accident. thanks for checking on that for me

 

[ChicagoGuy]

Ok Joe you may be onto something though im not sure whats going on.

When I used Isnumber for the cell containing 4/11/2017 i got FALSE. But when i look at the forma cells its listed as DATE and the format if 3/14/12. So how else do i make it a date vs a text? I am grabbing the dates from another sheet and pasting them in unformatted. I can then reformat that column but again when i look at it i see that it shows as date.

 

[ChicagoGuy]

AHHH SpillerBD that works. I dont know why it works but it works. Thank you so much. I didnt realize i could use the text to column as an overide to format. Ive been using the form Format cells

 

[Joe4]

Note that formatting the cells does NOT change/convert the content of the cells. All it does is change how numeric entries appear in the cells.
If your entries are text, formatting them will have no affect on them. You need to convert them to date (numbers). You can re-enter them, or use Text to Columns to do that.

 

[ChicagoGuy]

Thank Joe. That makes sense