+ Reply to Thread
Results 1 to 11 of 11

Employee salary calculation help!

  1. #1
    Registered User
    Join Date
    05-21-2015
    Location
    New York
    MS-Off Ver
    2013
    Posts
    9

    Employee salary calculation help!

    Hi all,

    New to the forum here. I'm looking for help writing a formula that will calculate monthly salary based on start date, end date, and salary raise date.

    I've attached an image of the columns with the inputs (in blue font) and shaded the cells I'd like the formulas to be written in.

    Capture.PNG

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Employee salary calculation help!

    Hi, welcome to the forum

    Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
    Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

    Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    05-21-2015
    Location
    New York
    MS-Off Ver
    2013
    Posts
    9

    Re: Employee salary calculation help!

    Got it, thanks for the tip. I attached the spreadsheet. The yellow shaded cells contain the output I'm looking for.

    Thanks all!
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Employee salary calculation help!

    This is what I have so far. I have to leave for a few minutes, will continue when I get back...
    =($H6/12)*IF(L$3>=$D6,1,($D6-L$3)/(EDATE(L3,1)-L3))

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Employee salary calculation help!

    OK 1 last bit to do (the year-end).

    This is what I have so far...
    =(($H6+IF(L$3>=$F6,$I6,0))+$I6*IF(EOMONTH($F6,-1)+1>L3,0,IF(EOMONTH($F6,-1)+1=L3,($F6-L$3)/(EDATE(L3,1)-L3),1)))/12
    This gives 3944.44 for the increase month, instead of 3958.33

  6. #6
    Registered User
    Join Date
    05-21-2015
    Location
    New York
    MS-Off Ver
    2013
    Posts
    9

    Re: Employee salary calculation help!

    Thank you! So to clarify, is this a separate formula for the increaes month? I was hoping to use one formula across all months if that makes sense.

  7. #7
    Registered User
    Join Date
    05-21-2015
    Location
    New York
    MS-Off Ver
    2013
    Posts
    9

    Re: Employee salary calculation help!

    To add one more wrinkle, it would be great if the formula would still work if the "End date" and "Raise date" columns were both blank and there was only a "Start date"

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Employee salary calculation help!

    No, that is the only formula you use.
    I will see what else I can come up with

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Employee salary calculation help!

    If there is no Raise Date, when would you want it applied?

    I had not yet built in the End date, should I just not bother with that part?

  10. #10
    Registered User
    Join Date
    05-21-2015
    Location
    New York
    MS-Off Ver
    2013
    Posts
    9

    Re: Employee salary calculation help!

    I think this does it. Not sure if there is a simpler way. Thank you very much for your help. This calculates all three scenarios and also ignores end date and raises when those fields are left blank.

    =($H6*IF(EOMONTH($D6,-1)+1>K3,0,IF(EOMONTH($D6,-1)+1=K3,((L$3-$D6)/(EDATE(K3,1)-K3))/12,1/12)))*IF($E6="",1,(IF(EOMONTH($E6,-1)+1<K3,0,IF(EOMONTH($E6,-1)+1=K3,($E6-K$3)/(EDATE(K3,1)-K3),1))))+$I6*IF($F6="",0,(IF(EOMONTH($F6,-1)+1>K3,0,IF(EOMONTH($F6,-1)+1=K3,($F6-K$3)/(EDATE(K3,1)-K3),1))/12*IF(EOMONTH($E6,-1)+1<K3,0,IF(EOMONTH($E6,-1)+1=K3,($E6-K$3)/(EDATE(K3,1)-K3),1))))

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Employee salary calculation help!

    Awesome, Im happy we got you where you needed to be.

    If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Problem in counting hours of work in employee salary form.
    By johansen.heine in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 01-06-2015, 05:47 PM
  2. Replies: 2
    Last Post: 05-27-2014, 01:17 AM
  3. [SOLVED] Annual employee salary increase - after one full year of service -- formula
    By macrorookie in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-21-2014, 04:26 PM
  4. Neep Help ! With Employee salary calculation spread sheet ! Very Hard!
    By lmplhk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-02-2014, 03:03 AM
  5. salary calculation
    By tariqnaz2005 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-01-2013, 03:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1