Date Combine from multiple cells

[Kindlysinful]

Hello all,

I have a problem and would appreciate any ideas. I have an excel sheet that in cell C5 the month and day are formatted as, “Aug 27”. In cell D5 the year and time is formatted as, “ 2021 11:23:45 PM”.

I need to combine the month, day and year in the following format, 08/27/2021. What I came up with so far. In cell L2 I have =datevalue(C5), which returns 08/27. In cell M2 I have =LEFT(D5,5) which returns 2021. In cell M4 I have =L2+M2 which returns 3/10/2027.????? Anyone know why the date is off?

Thank you in advance for any assistance

 

[jasonb75]

The formula =DATEVALUE(C5) will include the current year (this could be incorrect if the year in D5 is not the current year), you just need to change the cell format to show it. =L2+M2 is adding 2021 days, not joining the year to the date.

You could try =DATEVALUE(C5&" "&D5) instead which should give you the correct result.

edit:- actually that could be incorrect. It should work if your default system date format is mm/dd/yy (US date format) but will likely fail if your default system format is anything else.

 

[Kindlysinful]

Thank you jasonb75 for taking the time to answer. Unfortunaley, the formula =DATEVALUE(C5&" "&D5) returned #VALUE!

 

[jasonb75]

Please see my edit above, I don't see anything on your user profile to say where you are located so it may not work correctly with your system settings. Having done a quick test, I am not able to force an error from the example text in the cells.

A bit longer but see if this works instead.

=DATE(--LEFT(D5,5),MONTH(DATEVALUE(C5),DAY(DATEVALUE(C5))

 

[Dave Patton]

Format to your preference
You did not specify the default system date format on your system

N.B. My system is dd-mmm-yy .
If my formula doesn't work for you reverse this part 1&LEFT(C4,3) to LEFT(C4,3)&1

T202108b.xlsm
	A	B	C	D	E
1
2
3
4		C4 is Text	Aug 27	2021 11:23:45 PM	August 27, 2021 11:23:45 PM
5		C5 is Date formatted	Aug 27	2021 11:23:45 PM	August 27, 2021 11:23:45 PM
6
6c

Cell Formulas
Range	Formula
E4	E4	=DATE(LEFT(D4,4),MONTH(1&LEFT(C4,3)),RIGHT(C4,3))+RIGHT(D4,11)
E5	E5	=C5+RIGHT(D5,11)

 

[jasonb75]

You did not specify the default system date format on your system

From the testing that I did earlier, I'm sure that the OP's system format it mm/dd/yy.

If it was dd/mm/yy then their attempted formula would have returned 11/02/2033 instead of 3/10/2027

 

[Kindlysinful]

Thank you once again. I am in the USA, I didn't realize that region location would matter, that's interesting. That is what I like about these forums, there is always something to learn.

The formula provided was missing a couple ")". Not entierly certain why it flagged that. But once I put those in, WORKED BEAUTIFULLY. Here is what I ended up with:
=DATE(--LEFT(D5,5),MONTH(DATEVALUE(C5)),DAY(DATEVALUE(C5)))

Thank you very, very much

 

[jasonb75]

I am in the USA, I didn't realize that region location would matter, that's interesting.

Different countries format their dates in different ways. In numeric format it can cause inconsistency, 1/10/21 is January 10th in US format, but October 1st in UK format. With text dates, the month names may not be recognised, or other inconsistencies may happen depending on the way it is entered. Aug-27 for example is month and day in US format but it is month and year in UK format, which is the reason behind the differing results.

 

[Dave Patton]

I deleted the time and edited the formula.
To show the result in USA format, I used Text.
If your your date in C5 is custom formatted as mmm dd; you just have to format the cell to your preference.

T202108b.xlsm
	A	B	C	D	E	F
2
3
4		C4 is Text	Aug 27	2021 11:23:45 PM	27-Aug-21	08/27/2021
5		C5 is Date formatted mmm dd	Aug 27	2021 11:23:45 PM		08/27/2021
6
6c

Cell Formulas
Range	Formula
E4	E4	=DATE(LEFT(D4,4),MONTH(LEFT(C4,3)&1),DAY(RIGHT(C4,2)))
F4	F4	=TEXT(E4,"mm/dd/yyyy")
F5	F5	=TEXT(C5,"mm/dd/yyyy")