Good Day
Please can you assist. The report that I am using is a download from SAP via BI and I need to change the date format which is for example 07.2019 to 01/07/19. All the days can be 01 all I need is to get it into a date format. (The sales reporting is by month)
I am using Office 365.
I have attached the sample date data.
Thank you
Dave
Hi Dave,
Try it with this formula in e.g. C2:
=DATE(RIGHT(B2:B3624,4),LEFT(B2:B3624,2),1)
It will spill an array with dates into 3623 rows from C2 and down. Make sure that all cells below are blank to avoid the #SPILL! error.
Riny
Hi Dave,
If you prefer to do this in Power Query then you can split the date into month and year columns, then join them back together in a custom column with the #date function e.g.:
=#date([Year],[Month],1)
Mynda
