Forum Discussion
Date format error issue
PLEASE HELP.
I'd like to use "datedif" function, but for no apparent reason, I can't seem to get a result.
For example, I want to see the difference between 3/30/2022 and 3/21/2022 in terms of "days."
So I used "DATEDIF" function as follows:
Type 1: =DATEDIF(A12,H12,"D") --> #VALUE!
3/21/2022 3/30/2022
This time, I changed the date format by showing year first and even changed the slashes (/)
to the hiphens (-) and it worked.
Type 2: =DATEDIF(A18,H18,"D") --> 9
2022-03-21 2022-03-30
MY QUESTION, THEN, IS WHY TYPE 1 DOESN'T WORK?
I asked this because I have thousands of cells in my workbooks with dates expressed as in Type 1
and I can't seem to change the date formats to Type 2 to use the DATEDIF function at all.
Thanks in advance.
Sorry for the no uploaded workbook, as I couldn't find a way to upload the file in this site.
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.
18 Replies
- Locale in Data Type Menu
Click the Data Type box in the top-left corner of the column that contains the dates, then choose Using Locale
This will open the Change Type with Locale window.
Press OK to apply to change the data type to a date with the proper formatting.
Regards,
WillI guess that's about Power Query
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.
- By the way, may I ask in detail how to use Left/Right/Mid functions if I want to pull year in front of month and day? That way, I feel like I might have a shot at the datedif function. A little issue I face when I tried this was: What do I have to do when the month and the day come in single or double digits? For example:
If I use RIGHT function to pull out the year, I can go ahead and use =RIGHT(referred cell, 4). But what about the 3/30/? When the month changes to 12/30, then I have to change the LEFT function to =LEFT(referred cell,1,2). If I'd like to reflect this year swap onto all the past years, what blanket method can I use, regardless of the month digits (or day digits, for that matter)? You see my point?
Many thanks indeed.- Let's assume that TextDate = 3/31/2022.
Only4DigitYear = RIGHT(TextDate, 4)
AnyDigitYear =TRIM(RIGHT(SUBSTITUTE(TextDate,"/",REPT(" ",5)),5))
Month =TRIM(MID(SUBSTITUTE(I14,"/",REPT(" ",5)),5,5))
Day =LEFT(TextDate, FIND("/",TextDate)-1)
To get the actual Date value, the formula can be written as below.
Date=DATE(TRIM(RIGHT(SUBSTITUTE(TextDate,"/",REPT(" ",5)),5)),LEFT(TextDate, FIND("/",TextDate)-1),TRIM(MID(SUBSTITUTE(TextDate,"/",REPT(" ",5)),5,5)))
- Thank you, very much for the wonderful advice, Starrysky1988.
However, I find that the cells show they are correctly formatted. In the seemingly wrongly formatted cells, changing the location of the years (3/21/2022 --> 2022/3/21; 3/30/2022 --> 2022/3/30) makes the DATEDIF formula work, which is a big question mark for me. I tried the datevalue/value functions as you advised, but they simply didn't work. What do you suspect I did wrong?
In Excel a date is just a number (today is 44650) so the format shouldn't matter. It may be that your formula is not referencing dates at all but merely text that looks like a date in US format. The long and short date formats that a particular computer will recognise is set as part of the Windows language setting.
- Thanks for the kind reply, Peter. However, the cell with 3/30/2022 was in correct date format and I made sure the format was set to display dates. When I manually changed the sequence to show the year first--e.g. 2022/3/30--it did work. That's why I got puzzled. That simply changing the location of the years (3/21/2022 --> 2022/3/21; 3/30/2022 --> 2022/3/30) makes the formula work, is a big puzzle for me.
"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.
