PDA

View Full Version : Calculate Tenure in Power Query Customer Column



porter444
2016-10-25, 07:05 PM
Need to calculate employee tenure in a data set I'm working with, and would like to handle in Power Query. I have a column with the employee hire date.

The formula in Excel would be something like this... =(NOW()-[Hire Date])/365

Any help would be appreciated; thanks in advance!

Ken Puls
2016-10-26, 04:21 AM
Hi there,

See if this is what you're after: =(DateTime.LocalNow()-[Hire date])/365

porter444
2016-10-26, 12:36 PM
It's giving me an error. I tried adding a custom column [Now] with just the =DateTime.LocalNow() and then adding another column [Tenure] with =([Now]-[Hire Date])/365. The [Tenue] column has the error. Is this data type issue?

Ken Puls
2016-10-28, 06:02 AM
Yes, it would be. What's the data type of the Hire Date column?

You might try either of these:
=(DateTime.LocalNow()-DateTime.From([Hire date]))/365

Or
=(Number.From(DateTime.LocalNow()-Number.From([Hire date]))/365