OPQRSTUVWXYZAAAB
1
Scenario #1
1 W/E 07/24/211 W/E 07/24/21NOTE: depending on preference, the "1 W/E" can be added as a custom cell data formatting prefix via...
Ctrl + 1 > Custom > "1 W/E "mm/dd/yy
...which would enable you to preserve U1:U4 data formatting as dates
2
The 4 cells reference a flexible source (i.e., you can
1 W/E 08/07/211 W/E 08/07/21
3
define how the data is inputted, as to end up in
NO PROMONO PROMO
4
the format you gave in the OP)
NO PROMONO PROMO
5
08/07/21 to 07/24/21
6
FORMULATEXT() for V5
7
=IF(OR(ISBLANK(U1),ISBLANK(U2),ISBLANK(U3),ISBLANK(U4)),"",(TEXT((MAX(U1:U4)),"mm/dd/yy")&" to "&TEXT((MIN(U1:U4)),"mm/dd/yy")))
8
9
Scenario #2
10
The 4 cells reference a static source (i.e., the data
1 W/E 07/24/2107/24/2144401
11
pre-exists in the format given in the OP, and needs
1 W/E 08/07/2108/07/2144415
12
to be referenced as-is)
NO PROMO
13
NO PROMO
14
08/07/21 to 07/24/21
15
FORMULATEXT() for V14
16
=if((and(X10="NO PROMO",X11="NO PROMO",X12="NO PROMO",X13="NO PROMO")),("'NO PROMO'"),((TEXT((MAX(X10:X13)),"mm/dd/yy")&" to "&TEXT((MIN(X10:X13)),"mm/dd/yy"))))
17
FORMULATEXT() for column W
18
=IF(V10<>"NO PROMO",RIGHT(V10,8),"")
...and drag down
19
FORMULATEXT() for column X
20
=IF(LEN(W10)>0,DATEVALUE(W10),"")
...and drag down
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100