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.
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.
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)))
- Dear Starrysky1988,
I am extremely surprised that I can get this kind of expert advice this fast. I tried the formula and reference it to the relevent "textdate" cell and it worked! My sincere and humble gratitude to you!
A few notes, though:
1. The formulae above works perfect, but it sure is out of my ability to recreate something like it on my own. I must study them very closely going forward.
2. How come cell formatting wouldn't work? My thinking is that if I change the textdate cell to a date format from text, the complex formulae you kindly worked out wouldn't be needed in the first place.
Thanks anyway for your kind reply.
Frustrated Still.