Need formula for prorating monthly salary based on hire/fire date and annual salary.
Your response may be especially helpful if you're using Excel for Mac (2011). Some excel functions appear to not work the same when copy/pasted into Mac's Excel sheets.
Last edited by Excel_Help_Pls; 07-31-2015 at 09:56 PM. Reason: more detail required
If you fully explain one example of what you mean, then we can certainly suggest appropriate formulas to automate those same numbers.
I would be very interested to see some of these formulas you have experienced not working on a Mac. That's the first time I've heard anyone assert that.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Ok, I've attached a sample sheet to show what I need... Starting with the formula in cells G159 thru R235, I need to adapt these for use with the end of month date as shown (as opposed to the beginning of month date which the formula was originally written for) so that the full months of employment populate evenly every month. In other words, cells G159 thru R159 should all be $5,746 (or, $68,952 annual salary divided by 12).
Try this in G159:
=IF($F10>G$156, 0, IF(OR($G10=0, EOMONTH($G10,0)>=G$156), $E10/12, 0))
Thank you! Ok, I did. This formula is not prorating where the start/end date is mid-month...
That part I don't understand yet. Want to do that math for me on one or two and I'll match it?
the first (purple) tab looks the way the info needs to populate, but I need a single formula to allow for the three possibilities of partial month at beginning, partial month at end and full month.
Try this in G32:
=IF(OR(EOMONTH($F10,0)>G$29, AND($G10>0,EOMONTH($G10,0)<G$29)), 0, ($E10/12/DAY(G$29))*IF(EOMONTH($G10,0)=G$29, DAY(G$29)-DAY($G10), (DAY(G$29)-DAY(MAX($F10, EOMONTH(G$29, -1)+1))+1)))
BOOM!! That's it! She's a beauty) Sincerest thanks JBeaucaire!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks