amarbalani
New Member
- Joined
- Dec 4, 2017
- Messages
- 1
I currently havedates (calendar year) that are in 6 different formats and want to convert allthe different formats into quarters, within one formula
<tbody>
</tbody>
Actual data from Column D in worksheet:
<tbody>
</tbody>
Is there a singleformula that can be used to take the different "formats" from thetable above and can be used to convert to the "Expected Output(Quarter)", as outlined in the table above ?
Thank you
Format | Example | Expected output (Quarter) | Notes |
DD/MM/YYYY | 12/01/2017 | 4Q 2017 | |
MM/YYYY | 12/2017 | 4Q 2017 | |
XH YYYY | 1H 2017 2H 2017 | 1Q 2017 3Q 2017 |
|
YYYY | 2017 | 1Q 2017 | If standalone year default to 1Q YYYY |
XQ YYYY | 4Q 2017 | 4Q 2017 | |
YYYY-YYYY |
| 1Q 2018 | IF a range of years , default to 1Q of the first year in range |
<tbody>
</tbody>
Actual data from Column D in worksheet:
11/26/2018 |
12/15/2018 |
12/27/2018 |
4Q 2018 |
4Q 2018 |
2H 2018 |
2H 2018 |
2018 |
2018 |
2018 |
2018 |
2018 |
2018 |
2018 |
2018-2019 |
2018-2019 |
2018-2019 |
2018-2019 |
2018-2019 |
2018-2019 |
2018-2019 |
2018-2019 |
2018-2020 |
2018-2020 |
2018-2021 |
2018-2022 |
01/01/2019 |
01/03/2019 |
01/22/2019 |
02/17/2019 |
02/18/2019 |
02/27/2019 |
02/2019 |
02/2019 |
02/2019 |
03/04/2019 |
<tbody>
</tbody>
Is there a singleformula that can be used to take the different "formats" from thetable above and can be used to convert to the "Expected Output(Quarter)", as outlined in the table above ?
Thank you