IF functions, Date Formulas

[s.tara91]

Hi All,

I'm just hoping for a bit of help on a formula i need for a spreadsheet that i have attached.

In column AD starting in cell AD7 i need a formula that...

Looks at the Group Join Date (Column G) and if this date is before the Financial Year Start Date (Cell E2) then divide column AC by 9 and times by 12 to get an annualised amount.

But if the Group Join Date (Column G) is after than the Financial Year Start Date (Cell E2) column AC needs to be divided by how many months they have been here including the month they started then times by 12. So if the Financial Year Start Date is 01/07/13 and the Group Join Date is 01/10/13 then column AC will need to be divided by 4 then times by 12.

Im not sure if this needs to be done in 2 stages to figure out how many months people that have started after the financial year start date have worked here for before annualising but any help would be amazing.

Thanks!

Workplace Profile TEST.xlsx

[vipul.halbe]

Hi,

This try this formula

=+IF($E$2>G9,U9/9*12,U9*12/((YEAR(G9)-YEAR($E$2))*12+(MONTH(G9)-MONTH($E$2))))

Hope it helps