Forum Discussion
Working with non-standard date fields
Hi Danbert,
I am confused. The formula below (which is from the code above) does what you are asking and does not require editing.
Regards,
Mike
Date.AddMonths(#date(2000+Number.From(Text.Start([Year],2)),Number.From(Text.Start([Month],2)),1),4)
Hi Danbert,
This problem is easier then it appears on first glance. The approach is to 1) create a date where the year is the first two digits of the Year and the first two digits of the month. This now converts May to Jan-2018, June to Feb-2018, ..., March to Nov-2018 and April to Dec-2018. Now 2) add four months to the date to get May-2018,Jun-2018, .., Mar-2019 and Apr-2019. The method is implemented as a custom column below.
Regards,
Mike
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
AddYear = Table.AddColumn(Source, "Date", each Date.AddMonths(#date(2000+Number.From(Text.Start([Year],2)),Number.From(Text.Start([Month],2)),1),4))
in
AddYear
- Hi MikeMany thanks for your reply.I managed to get around the year issue by adding a custom column (I am using Power BI). It will need editing as time goes by but it will suffice for now.
=if ([Month] = "01-May" or [Month] = "02-June" or [Month] = "03-July" or [Month] = "04-August" or [Month] = "05-September" or [Month] = "06-October" or [Month] = "07-November" or [Month] = "08-December") and [Year] = "18/19" then 2018 else if ([Month] = "09-January" or [Month] = "10-February" or [Month] = "11-March" or [Month] = "12-April") and [Year] = "18/19" then 2019 else if ([Month] = "01-May" or [Month] = "02-June" or [Month] = "03-July" or [Month] = "04-August" or [Month] = "05-September" or [Month] = "06-October" or [Month] = "07-November" or [Month] = "08-December") and [Year] = "19/20" then 2019 else if ([Month] = "09-January" or [Month] = "10-February" or [Month] = "11-March" or [Month] = "12-April") and [Year] = "19/20" then 2020 else if ([Month] = "01-May" or [Month] = "02-June" or [Month] = "03-July" or [Month] = "04-August" or [Month] = "05-September" or [Month] = "06-October" or [Month] = "07-November" or [Month] = "08-December") and [Year] = "20/21" then 2020 else if ([Month] = "09-January" or [Month] = "10-February" or [Month] = "11-March" or [Month] = "12-April") and [Year] = "20/21" then 2021 else if ([Month] = "01-May" or [Month] = "02-June" or [Month] = "03-July" or [Month] = "04-August" or [Month] = "05-September" or [Month] = "06-October" or [Month] = "07-November" or [Month] = "08-December") and [Year] = "21/22" then 2021 else if ([Month] = "09-January" or [Month] = "10-February" or [Month] = "11-March" or [Month] = "12-April") and [Year] = "21/22" then 2022 else 0
I have split the month field as mentioned so I now have a 'Standard Month' column (text formatted) with 'January, February, March etc...', and a 'Standard Year' column (date formatted) with '2019, 2020, 2021 etc...'What I need to do now is combine those fields into a single date field, so I can compare with other date fields. If the format has to include a day of the month then that's fine (I would just have the first day of each month). It would be great if you could point me in the direction of how to do that?I created a further custom field (converting the Months to Numbers), merged the new field and the Year field together and converted to date format. They showed up in the format 01/02/2019, 01/03/2019 etc, but perfect for my needs.
Hi Danbert,
I am confused. The formula below (which is from the code above) does what you are asking and does not require editing.
Regards,
Mike
Date.AddMonths(#date(2000+Number.From(Text.Start([Year],2)),Number.From(Text.Start([Month],2)),1),4)
