RAJESH1960
Banned for repeated rules violations
- Joined
- Mar 26, 2020
- Messages
- 2,313
- Office Version
- 2019
- Platform
- Windows
Hello guys
I have this data which I converted pdf to Excel. The dates have been separated in 2 parts by a row. I am trying to join these dates. I tried this formula to join text, but as the data is a date I am not able to get the correct dates. How can I get the correct format of the date in column E.?
I have this data which I converted pdf to Excel. The dates have been separated in 2 parts by a row. I am trying to join these dates. I tried this formula to join text, but as the data is a date I am not able to get the correct dates. How can I get the correct format of the date in column E.?
query to join dates in different rows.xlsx | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | Balance | Value | Value | ||||
2 | 62964.66 | 09-Apr-20 | 43930 | ||||
3 | |||||||
4 | |||||||
5 | |||||||
6 | |||||||
7 | 63249.66 | 09-Apr-20 | 43930 | ||||
8 | |||||||
9 | |||||||
10 | |||||||
11 | |||||||
12 | 63517.66 | 09-Apr-20 | 43930 | ||||
13 | |||||||
14 | |||||||
15 | |||||||
16 | |||||||
17 | 63717.66 | 09-Apr-20 | 43930 | ||||
18 | |||||||
19 | |||||||
20 | |||||||
21 | |||||||
22 | 63996.66 | 10-Apr | 442962020 | ||||
23 | 2020 | ||||||
24 | |||||||
25 | |||||||
26 | |||||||
27 | |||||||
28 | |||||||
29 | 64011.66 | 10-Apr | 442962020 | ||||
30 | 2020 | ||||||
31 | |||||||
32 | |||||||
33 | |||||||
34 | 64229.66 | 10-Apr | 442962020 | ||||
35 | 2020 | ||||||
36 | |||||||
37 | |||||||
38 | |||||||
39 | 64447.66 | 10-Apr | 442962020 | ||||
40 | 2020 | ||||||
Get full date in one row |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E1:E40 | E1 | =IF(C1="","",CONCAT(D1:INDEX(D:D,IFERROR(AGGREGATE(15,6,ROW(C1:C9)/(C2:C10<>""),1),ROW()+10)))) |