Only Date in separate column

[sksanjeev786]

Hi Team,

I have tried to fix the date in a separate column and facing a value error.
Can you please let me know how to fix the below formula?

Book1
	A	B	C
1
2		11/21/2020	21/11/2020
3	20	11/21/2020	#VALUE!
4	19	11/21/2020	#VALUE!
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=TODAY()
C2	C2	=TEXT(B2,"d"&"/"&"m"&"/"&"yYYY")
C3:C4	C3	=TEXT(B3,DAY(A3)&"/"&"m"&"/"&"yYYY")

Regards,
Sanjeev

 

[mohadin]

C3=TEXT(DAY(A3)&"/"&MONTH(B3)&"/"&YEAR(B3),"dd/mm/yyyy")

 

[Peter_SSs]

Is this what you are trying to achieve?

20 11 21.xlsm
	A	B	C
1
2		21/11/2020	21/11/2020
3	20	21/11/2020	20/11/2020
4	19	21/11/2020	19/11/2020
Date Format

Cell Formulas
Range	Formula
B2:B4	B2	=TODAY()
C2:C4	C2	=TEXT(DATE(YEAR(B2),MONTH(B2),IF(A2="",DAY(B2),A2)),"d/m/yyyy")

 

[sksanjeev786]

Is this what you are trying to achieve?

20 11 21.xlsm
	A	B	C
1
2		21/11/2020	21/11/2020
3	20	21/11/2020	20/11/2020
4	19	21/11/2020	19/11/2020
Date Format

Cell Formulas
Range	Formula
B2:B4	B2	=TODAY()
C2:C4	C2	=TEXT(DATE(YEAR(B2),MONTH(B2),IF(A2="",DAY(B2),A2)),"d/m/yyyy")

Perfect !!Thanks Peter

 

[Peter_SSs]

Cheers. Glad to help.

 

[sksanjeev786]

C3=TEXT(DAY(A3)&"/"&MONTH(B3)&"/"&YEAR(B3),"dd/mm/yyyy")

Hey Mohadin,

Sorry for the late response. you formula work perfectly

Thank you for your help on this.

Regards,
Sanjeev

 

[Peter_SSs]

Perfect !!Thanks Peter

Hey Mohadin,

.... you formula work perfectly

Did I misunderstand?
I thought that the formula would have to apply to data like your row 2 as well as rows like 3 & 4. My formula & mohadin's give different results for data like that.

20 11 21.xlsm
	A	B	C	D
1			post #3	post #2
2		23/11/2020	23/11/2020	0/11/2020
3	20	23/11/2020	20/11/2020	20/11/2020
4	19	23/11/2020	19/11/2020	19/11/2020
Date Format

Cell Formulas
Range	Formula
B2:B4	B2	=TODAY()
C2:C4	C2	=TEXT(DATE(YEAR(B2),MONTH(B2),IF(A2="",DAY(B2),A2)),"d/m/yyyy")
D2:D4	D2	=TEXT(DAY(A2)&"/"&MONTH(B2)&"/"&YEAR(B2),"dd/mm/yyyy")

 

[sksanjeev786]

Did I misunderstand?
I thought that the formula would have to apply to data like your row 2 as well as rows like 3 & 4. My formula & mohadin's give different results for data like that.

20 11 21.xlsm
	A	B	C	D
1			post #3	post #2
2		23/11/2020	23/11/2020	0/11/2020
3	20	23/11/2020	20/11/2020	20/11/2020
4	19	23/11/2020	19/11/2020	19/11/2020
Date Format

Cell Formulas
Range	Formula
B2:B4	B2	=TODAY()
C2:C4	C2	=TEXT(DATE(YEAR(B2),MONTH(B2),IF(A2="",DAY(B2),A2)),"d/m/yyyy")
D2:D4	D2	=TEXT(DAY(A2)&"/"&MONTH(B2)&"/"&YEAR(B2),"dd/mm/yyyy")

Hey Peter,
Sorry for the delay.

I have check both formula and work well as per my requirement.

Thanks for checking at your end as well.

Regards,
Sanjeev

 

[Peter_SSs]

I have check both formula and work well as per my requirement.

In that case if you are happy with either of the results in row 2 of my previous post, what about this simpler formula in column E?

20 11 21.xlsm
	A	B	C	D	E
1			post #3	post #2	post #9
2		28/11/2020	28/11/2020	0/11/2020	0/11/2020
3	20	28/11/2020	20/11/2020	20/11/2020	20/11/2020
4	19	28/11/2020	19/11/2020	19/11/2020	19/11/2020
Date Format

Cell Formulas
Range	Formula
B2:B4	B2	=TODAY()
C2:C4	C2	=TEXT(DATE(YEAR(B2),MONTH(B2),IF(A2="",DAY(B2),A2)),"d/m/yyyy")
D2:D4	D2	=TEXT(DAY(A2)&"/"&MONTH(B2)&"/"&YEAR(B2),"dd/mm/yyyy")
E2:E4	E2	=--A2&TEXT(B2,"\/m/yyyy")

 

[sksanjeev786]

In that case if you are happy with either of the results in row 2 of my previous post, what about this simpler formula in column E?

20 11 21.xlsm
	A	B	C	D	E
1			post #3	post #2	post #9
2		28/11/2020	28/11/2020	0/11/2020	0/11/2020
3	20	28/11/2020	20/11/2020	20/11/2020	20/11/2020
4	19	28/11/2020	19/11/2020	19/11/2020	19/11/2020
Date Format

Cell Formulas
Range	Formula
B2:B4	B2	=TODAY()
C2:C4	C2	=TEXT(DATE(YEAR(B2),MONTH(B2),IF(A2="",DAY(B2),A2)),"d/m/yyyy")
D2:D4	D2	=TEXT(DAY(A2)&"/"&MONTH(B2)&"/"&YEAR(B2),"dd/mm/yyyy")
E2:E4	E2	=--A2&TEXT(B2,"\/m/yyyy")

Hey Peter,

I understood what you wanted to point out.
yes if we don't have data on column A it will throw me "0" Date.

Thanks for checking
Regards,
Sanjeev