dates not working on one spreadsheet??

ChicagoGuy

New Member
Joined
May 12, 2017
Messages
14
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[TABLE="width: 500"]
<tbody>[TR]
[TD]8/1/2017[/TD]
[/TR]
[TR]
[TD]3/21/17[/TD]
[/TR]
[TR]
[TD]4/11/2017[/TD]
[/TR]
[TR]
[TD]4/18/2017[/TD]
[/TR]
[TR]
[TD]4/11/2017[/TD]
[/TR]
</tbody>[/TABLE]


Here is the result using the same formula on my current spreadsheet
Using the following formula =MIN(H32:H38) I get 1/0/1900[TABLE="width: 500"]
<tbody>[TR]
[TD]8/1/2017[/TD]
[/TR]
[TR]
[TD]3/21/17[/TD]
[/TR]
[TR]
[TD]4/11/2017[/TD]
[/TR]
[TR]
[TD]4/18/2017[/TD]
[/TR]
[TR]
[TD]4/11/2017[/TD]
[/TR]
</tbody>[/TABLE]


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. :mad::mad:

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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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
 
Last edited:
Upvote 0
Maybe on the one you are having trouble with, your dates are text rather than numbers. What does =ISNUMBER(H32) return?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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
Back
Top