Date format issue

[coryh83]

I know this should be simple but its throwing me for a loop.

I have a report that is sent to me that has a funky number for a due date. Example 1200721(7/21/2020). I need to convert this cell to a actual date format and be able to format it as a date. I also have another cell that will need to show how many days past the due date based on =Now()

Thanks for your help

 

[KOKOSEK]

Is it always 7 digits? I mean syntax like: XYYMMDD? If yes it is easy to deal with.

 

[Dave Patton]

1200721(7/21/2020) what is the 1200 supposed to represent.
Could you post a few more examples?

The second example ignores the 1200 and assumes the current year hard coded.

T202007a.xlsm
	B	C
1	12000721	21-Jul-20
2	12000721	21-Jul-20
3	12000721	21-Jul-20
2a

Cell Formulas
Range	Formula
C1	C1	=DATE(LEFT(B1,4)+820,MID(B1,5,2),RIGHT(B1,2))
C2	C2	=DATE(2020,MID(B2,5,2),RIGHT(B2,2))
C3	C3	=DATE(YEAR(TODAY()),MID(B3,5,2),RIGHT(B3,2))

 

[MARK858]

@Dave Patton , in your examples in XL2BB you have 3 0's, the original has only 2.

 

[Dave Patton]

Thanks Mark
I made a wild guess that the month part is 2 characters 07; please edit if the month is just 1 character.

T202007a.xlsm
	B	C
2	1200721	21-Jul-20
3	1200721	21-Jul-20
4
2a

Cell Formulas
Range	Formula
C2	C2	=DATE(2020,MID(B2,4,2),RIGHT(B2,2))
C3	C3	=DATE(YEAR(TODAY()),MID(B3,4,2),RIGHT(B3,2))

 

[MARK858]

I made a wild guess that the month part is 2 characters 07

With the above assumption I think C1 should be

Book1
	B	C
1	1200721	7/21/2020
Sheet1

Cell Formulas
Range	Formula
C1	C1	=DATE(LEFT(B1,3)+1900,MID(B1,4,2),RIGHT(B1,2))

 

[coryh83]

Is it always 7 digits? I mean syntax like: XYYMMDD? If yes it is easy to deal with.

yes this is always the syntax

 

[coryh83]

1200721(7/21/2020) what is the 1200 supposed to represent.
Could you post a few more examples?

The second example ignores the 1200 and assumes the current year hard coded.

T202007a.xlsm
	B	C
1	12000721	21-Jul-20
2	12000721	21-Jul-20
3	12000721	21-Jul-20
2a

Cell Formulas
Range	Formula
C1	C1	=DATE(LEFT(B1,4)+820,MID(B1,5,2),RIGHT(B1,2))
C2	C2	=DATE(2020,MID(B2,5,2),RIGHT(B2,2))
C3	C3	=DATE(YEAR(TODAY()),MID(B3,5,2),RIGHT(B3,2))

thank you c1 works for me.

 

[MARK858]

@coryh83 If you have the date 21st December 2020 would it be 1201221?
If it is then I think you get the result in C1 below with that formula, whereas I think you need the result in C2

Both formulas Dave posted in post #5 would yield the correct result (Column E below).

Dates.xlsb
	B	C	D	E
1	1201221	10/21/2022
2	1201221	12/21/2020		12/21/2020
3	1201221			12/21/2020
Sheet1

Cell Formulas
Range	Formula
C1	C1	=DATE(LEFT(B1,4)+820,MID(B1,5,2),RIGHT(B1,2))
C2	C2	=DATE(LEFT(B1,3)+1900,MID(B1,4,2),RIGHT(B1,2))
E2	E2	=DATE(2020,MID(B2,4,2),RIGHT(B2,2))
E3	E3	=DATE(YEAR(TODAY()),MID(B3,4,2),RIGHT(B3,2))

 

[Dave Patton]

I agree with Mark
Earlier post stated "Also the date may be clearer with the confirmation
Is it always 7 digits? I mean syntax like: XYYMMDD? If yes it is easy to deal with.
yes this is always the syntax"

To make up for previous post, you could try the latest suggestion.
Instead of showing the long number, I named it Num see Name Manager Num Refers to: =19000000

T202007a.xlsm
	B	C
1	1200721	21-Jul-20
2	1201221	21-Dec-20
3	1201221	21-Dec-20
2a

Cell Formulas
Range	Formula
C1	C1	=DATE(LEFT(B1,3)+1900,MID(B1,4,2),RIGHT(B1,2))
C2	C2	=--TEXT(B2+19000000,"0000-00-00")
C3	C3	=--TEXT(B3+Num,"0000-00-00")