Months Between Two Dates Syntax

[Simonc64]

Hi All Im a complete novice using power query in Excel and giving it a go, but this has stumped me.

Im trying to find the number of months between a persons date of birth and TODAY() - and then how to turn that into a variable

Any help welcomed - thank you !

 

[alansidman]

id	first_name	DOB	Monhs
1	Ira	5/13/2007	195
2	Veronique	8/24/2008	180
3	Carole	6/6/2011	147
4	Giovanni	11/30/2011	141
5	Drucie	9/4/2016	84
6	Gordan	8/13/2014	108
7	Laure	10/27/2019	46
8	Percy	8/17/2003	240
9	Jaye	10/28/2004	226
10	Tamas	12/21/2015	92
11	Ibby	1/2/2011	152
12	Danny	3/19/2020	41
13	Merrile	11/18/2013	117
14	Flss	12/13/2019	44
15	Connie	8/14/2010	156
16	Elita	2/9/2009	174
17	Aili	5/25/2004	231
18	Marena	7/25/2007	193
19	Sandro	12/14/2012	128
20	Bradley	12/7/2013	117
21	Alta	8/17/2003	240
22	Daron	7/2/2002	254
23	Vale	12/28/2001	260
24	Analise	11/12/2016	81
25	Gelya	10/22/2019	46

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DOB", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Today", each DateTime.LocalNow()),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Today", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Months", each Number.Round(Number.From(([Today] - [DOB])/( 365.25 / 12 )) ,0 )),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Today"})
in
    #"Removed Columns1"

 

[Simonc64]

Thanks Alansidman - i will give that a go

 

[smozgur]

A little bit different than @alansidman's nice solution. Using the Power Query interface.

You have the following table in a worksheet:

While you have a cell selected in this range, Ribbon->Data->Get Data->From Other Sources->From Table/Range (note that there is also an additional button on the ribbon for this option)

Confirm the table creation with the headers option is selected:

The Power Query interface is opened:

Click on the Add Column ribbon tab, and enter Months for the New column name, then copy and paste the following formula into the formula editor box:

(Date.Year(DateTime.LocalNow())-Date.Year([Date of birth]))*12 + Date.Month(DateTime.LocalNow()) - Date.Month([Date of birth])

Click OK, and you are done.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"Date of birth", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Months", each (Date.Year(DateTime.LocalNow())-Date.Year([Date of birth]))*12 + Date.Month(DateTime.LocalNow()) - Date.Month([Date of birth]))
in
    #"Added Custom"

 

[Simonc64]

A little bit different than @alansidman's nice solution. Using the Power Query interface.

You have the following table in a worksheet:
View attachment 97750

While you have a cell selected in this range, Ribbon->Data->Get Data->From Other Sources->From Table/Range (note that there is also an additional button on the ribbon for this option)
View attachment 97751

Confirm the table creation with the headers option is selected:
View attachment 97752

The Power Query interface is opened:
View attachment 97755

Click on the Add Column ribbon tab, and enter Months for the New column name, then copy and paste the following formula into the formula editor box:

(Date.Year(DateTime.LocalNow())-Date.Year([Date of birth]))*12 + Date.Month(DateTime.LocalNow()) - Date.Month([Date of birth])

View attachment 97756
Click OK, and you are done.

View attachment 97757

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"Date of birth", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Months", each (Date.Year(DateTime.LocalNow())-Date.Year([Date of birth]))*12 + Date.Month(DateTime.LocalNow()) - Date.Month([Date of birth]))
in
    #"Added Custom"

Thank you so much for this alternative - I have a lot to learn - I will try this way too !