Format date

[FrankE67]

Hi all. It's me again. I can't seem to find the thread i got the answer from, i have done it before but can't remember how now. I want to insert date in the format yyyymmdd (input as 2020220) in c3. in c4 i want to put , let's say a 6, in c5 i want to add the two together and get a yyyymmdd result. i can't find a way to do it without inputing 2020/02/20 in c3. am i just getting old? how can i format it so it takes the 20200220 as date?

 

[Cubist]

Odd request but try:

Book1
	C	D
1
2
3	20200220	<-General
4	6	<-General
5	20200226	<-Formatted as yyyymmdd
Sheet1

Cell Formulas
Range	Formula
C5	C5	=C3-20156339+C4

 

[FrankE67]

@Cubist That gives me a #NUM error in C5

 

[Cubist]

One of your cells is text not numbers.

 

[FrankE67]

i see what the issue is. where did the 20156339 come from in the formatting for C5

 

[Cubist]

I've discovered that the above won't work for all cases. This is more robust.

Book1
	C	D
1
2
3	20200101	<-General
4	6	<-General
5	20200107	<-Formatted as yyyymmdd
Sheet1

Cell Formulas
Range	Formula
C5	C5	=--(MID(C3,5,2)&"/"&RIGHT(C3,2) &"/"&LEFT(C3,4))+C4