An Annual Salary into a Monthly Salary Given a Start Date

**shaero** · 07-26-2012, 03:54 PM

Hi Folks,

I'm trying to figure out a formula (or, perhaps, a set of formulas operating on each other) that would work on two cells (the start date, e.g., 7/26/2012, and salary, say $120,000) and allocate the amounts into each month (I have separate cells for the months as well). Going back to the example above, say that my start date in cell A1 is 7/26/2012 and annual salary in cell B1 is $120,000. Now I'm trying to allocate the salary into each of the monthly cells (January 2012 in C1, February 2012 in D1, March 2012 in E1, etc.), but because that person is starting only on 26 July, I would like the allocations to begin from July as opposed to January. So from July to December we would have $10,000 in each cell. Maybe someone could suggest some formula or a user defined function for this? I've completely ran out of ideas.

Cheers,
shaero

**CheshireCat** · 07-26-2012, 03:59 PM

What value would you want displayed for cel I1 (July 2012)?

**smls** · 07-26-2012, 04:08 PM

I'm not sure exactly what youre looking for, from what I understand I tihnk this might help.

Book2.xlsx

Meant to add a row title of "pay day" or "new pay month" for the dates since I am calculating ~every month from the start date. You will need to change Feb+ though since I forgot to take into account a 28 day month, just take a couple days off each month

**The Nash Effect** · 07-26-2012, 04:11 PM

I calanderized out the days left in the year. Then assigned a month value in an additional cell. Then wrote a nested if to represent the month. Theres probably a formula that does that automatically but I don't know it off the top of my head. Next I divided your salary by 365 to attribute it evenly across the monthly pay periods to project how much you will earn over the course of the year, in each month starting on the 26th of this month. From there I made a pivot table and just multiplied the days of the month by the daily pay rate.

120000.xlsx