Converting 29 February Dates of Birth to 1 March

[Paul_s_Moody]

Hi

I'm working with pension scheme data and am using Date of Birth field to add a new column with the UK State Pension Age (SPA).

I'm using a dynamic replace tool as the calculation of SPA depends on the individuals birthday or reaching a specific date depending on the actual date of birth.

My table works fine for all of the test data, except for those individuals born on 29 February - i.e. a leap year, where the SPA  is being calculated 1 day earlier than the regulations expect.

I think that the issue is the DateTimeAdd(...) function behaves "kindly" like in the real world assuming that those with a 29 February birthday would celebrate on the 28 February. However for UK SPA, the birthday is assumed to be 1 March...

For example, for an individual with DOB=29/2/1968, they should receive their state pension on their 67th birthday, which is being calculated as 28/2/2035 as 2035 isn't a leap year, but the regulation assumes that their 67th birthday is 01/03/2035.

I'm therefore trying to add a new column SPA_DoB that will adapt the leap year dates of birth appropriately, and then use this new column in the dynamic replace tool.

This is straightforward in VBA, using the Day(...) and Month(...) functions

Public Function SPA_DoB(MyDoB As Date)
If Day(MyDoB) = 29 And Month(MyDoB) = 2 Then
SPA_DoB = DateAdd("d", 1, MyDoB)
Else
SPA_DoB = MyDoB
End If
End Function

but I'm struggling to find existing functions in Alteryx designer that will allow me to look at the Day/Month components of the date separately in the IF clause before using a DateTimeAdd([DOB],1,"days") function to add the extra day.

Thanks in advance,

Paul

[phottovy · Accepted solution]

Hi @Paul_s_Moody:

I believe this is the formula you are looking for:

IF DateTimeDay([MyDoB]) = 29 AND DateTimeMonth([MyDoB]) = 2 
THEN DateTimeAdd([MyDoB], 1, 'days') 
ELSE [MyDoB] 
ENDIF

[BS_THE_ANALYST · Accepted solution]

@Paul_s_Moody 

Here's one way:

[Paul_s_Moody]

Thank you both!

I was hoping there would be a simple solution using existing Alteryx functions.

Paul