Forum Discussion
Date format error issue
Whether month or day comes first in a date is very important when you are working with the dates. When you type the date in Excel, the sequence of day and moth must always be the same as your operation system date format. Year can be first or last only by typing. If you want to see year in the middle, you need to apply custom format like "dd yyy mm" or "mm yyy dd".
3/21/2022 3/30/2022 is month and day sequence, ignoring year.
2022-03-21 2022-03-30 is the same as above.It should work the same for both if the dates are correct date formats.
WHY TYPE 1 DOESN'T WORK? And you got value error #value for the first one.That means the date is wrong and it is not the date format and has no value.
As Mr. Peter has explained your date may not be actual date format and it may be text format that represents or looks like the date or it may be text only with wrongly typed date and month sequence.If the date is text format with correct sequence, you may use Datevalue or Value function to convert text to actual date format like below.
Datevalue("3/30/2022") or Value("3/30/2022")
Datevalue("3/21/2022") or Value("3/21/2022")
If the date are wrongly typed in day and month sequence, you may need to use Left, Right, Mid Functions to swap day and month correctly as per the system date.
The syntax of Datedif function is as below.
=Datedif(start_date, end_date, interval_unit)*StartDate must be minimmum date.
*EndDate must be maximum date.
"the cell with 3/30/2022 was in correct date format and ... the format was set to display dates"
I am pretty certain that the date was actually text and the number format therefore had no effect. Putting the year first resulted in a valid international date format "yyyy/mm/dd" and the act of re-entering it gave Excel another opportunity to interpret it as a date if it could.
In Excel, any date is stored as the number of days elapsed since the first day of the year 1900; that is, a number in the region of 44650. What is displayed in the cell is something entirely different and depends upon the number/date format.
Something that could be helpful is to apply the formula
= LEFT(date, 2)
to one of your dates. If 12/12/2021 gives rise to 12, then the date is text and would, by default, appears the the left of the cell. If it returns 44 then it was a 'datevalue' and would default to the right of the cell.
- Dear Peter,
As you suggested, I tried the LEFT(date, 2) and the textdate cell sure gave me the digits as you suspected. The correct datevalue cell gave me the number 44.
Again, changing the textdate cell (e.g. 03/30/2022 which returns 03 in LEFT(textdate, 2)) to a datevalue cell would simply be done if I change the cell formats, but it didn't work.
For a small number of dates, I'd be happy to manually type year first followed by month and date. But I have thousands of text dates that I have to change to datevalue format. I sure learned how to change textdates to datevalues with Starrysky1988's grateful formulae, but I'm wondering if you have any simpler solution.
Many thanks for your kindness.The simplest solution I can come up with for dates that are stuck as text would be
= DATEVALUE(RIGHT(date,4)&"/"&LEFT(date, LEN(date)-5))which does as you did manually, namely, moves the year to the start to give an ISO formatted date which should convert to a datevalue.
A different solution is required if Excel has recognised some of your values as dates because the chances are the it will have returned the wrong date.
- Wow, this is perfect!!! Thanks a lot for your time. I was able to resolve all the text cell issues thanks to your formula! :^)