+ Reply to Thread
Results 1 to 17 of 17

How to automatically derive the Monthly Salary using the STart/exit dates & Unpaid Leave

  1. #1
    Registered User
    Join Date
    10-29-2019
    Location
    Berlin, Germany
    MS-Off Ver
    MS 365 64 Bit
    Posts
    13

    How to automatically derive the Monthly Salary using the STart/exit dates & Unpaid Leave

    Dear Team,

    New to the Platform so I hope I am doing this the right way.

    I want to create an excel sheet which is able to calculate the Monthly Salaries of employees based on their entry(start) dates and also if the said employee was on Unpaid Leave or not.

    FOr example in the attached sheet, Granny Bear who started in January 15th, was on Unpaid leave from March to end of May which means the company will not have to pay her for March, April and May.

    How can I create a formula that is able to calculate the monthly salary simply based on the data in this table and the conditions explained above?
    Same goes for the Headcount, I want to create a formula that returns a 1 for each month the employee was fully working.

    Will really appreciate any ideas on this.

    Many thanks!

    KB
    Attached Files Attached Files
    Last edited by Kay Bee; 10-31-2019 at 04:34 PM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: How to Automatically calculate the Monthly Salaries using the Headcount & Unpaid Leave

    Welcome to the forum.

    Please update your forum profile to tell us the version of Office (Excel) that you are using. Your Windows version is not what we need to know.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    10-29-2019
    Location
    Berlin, Germany
    MS-Off Ver
    MS 365 64 Bit
    Posts
    13

    Re: How to Automatically calculate the Monthly Salaries using the Headcount & Unpaid Leave

    Yikes. Just did it. Thanks AliGW

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: How to Automatically calculate the Monthly Salaries using the Headcount & Unpaid Leave

    Will there be only one span of unpaid leave. If more spans are there how you show. If unpaid leave start and end in middle of month how you calculate salary. Manually show the results and upload a ample file.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  5. #5
    Registered User
    Join Date
    10-29-2019
    Location
    Berlin, Germany
    MS-Off Ver
    MS 365 64 Bit
    Posts
    13

    Re: How to Automatically calculate the Monthly Salaries using the Headcount & Unpaid Leave

    Hey,

    thanks for the feedback. If the unpaid leave ends in the middle of the month the employee will be paid half his/her monthly salary. I have updated my attached example (manually entered figures). Kindly take a look.

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: How to Automatically calculate the Monthly Salaries using the Headcount & Unpaid Leave

    Where is the file.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: How to Automatically calculate the Monthly Salaries using the Headcount & Unpaid Leave

    Kay Bee - your workbook has disappeared - please attach it again and leave it attached. Thanks.

  8. #8
    Registered User
    Join Date
    10-29-2019
    Location
    Berlin, Germany
    MS-Off Ver
    MS 365 64 Bit
    Posts
    13

    Re: How to Automatically calculate the Monthly Salaries using the Headcount & Unpaid Leave

    my bad. uploaded the file but forgot to save changes. Just uploaded it again and seems i nailed it this time.

  9. #9
    Registered User
    Join Date
    10-29-2019
    Location
    Berlin, Germany
    MS-Off Ver
    MS 365 64 Bit
    Posts
    13

    Re: How to Automatically calculate the Monthly Salaries using the Headcount & Unpaid Leave

    just uploaded it again. sorry made a mistake earlier.

  10. #10
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: How to Automatically calculate the Monthly Salaries using the Headcount & Unpaid Leave

    PL notr the change in P6:T6

    In P7 and copied across

    =IF(AND($F7>0,$G7>0),($K7/$J7)*((DAY(EOMONTH(P$6,0))-MAX(0,MIN($G7,EOMONTH(P$6,0))-MAX($F7,P$6)+1))/DAY(EOMONTH(P$6,0))),$K7/$J7)
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    10-29-2019
    Location
    Berlin, Germany
    MS-Off Ver
    MS 365 64 Bit
    Posts
    13

    Re: How to Automatically calculate the Monthly Salaries using the Headcount & Unpaid Leave

    Thanks a lot for your efforts KV!

    From the one you just created I noticed that your formula doesnt take into account the ENTRYDATES and EXITDATES.

    For the first row for instance, the employee starts working from March 1st, 2019 but your formula already shows in cells P7 and Q7 that she is getting paid for January and February respectively even though she wasnt employed yet.

    I could see this being a big problem. Any idea how I can integrate the start and exit dates into your formula as well?
    Attached Files Attached Files
    Last edited by Kay Bee; 10-31-2019 at 04:29 PM. Reason: Add a question

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: How to Automatically calculate the Monthly Salaries using the Headcount & Unpaid Leave

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Please also leave the attachment, as it is integral to the usefulness of this thread to others later on. Thanks.

  13. #13
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: How to automatically derive the Monthly Salary using the STart/exit dates & Unpaid Lea

    In P7 and then copied across
    Please Login or Register  to view this content.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    10-29-2019
    Location
    Berlin, Germany
    MS-Off Ver
    MS 365 64 Bit
    Posts
    13

    Re: How to automatically derive the Monthly Salary using the STart/exit dates & Unpaid Lea

    perfect. thanks again KV

  15. #15
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: How to automatically derive the Monthly Salary using the STart/exit dates & Unpaid Lea

    Welcome to you.

  16. #16
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: How to automatically derive the Monthly Salary using the STart/exit dates & Unpaid Lea

    Pl see revised formula

    Please Login or Register  to view this content.

  17. #17
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: How to automatically derive the Monthly Salary using the STart/exit dates & Unpaid Lea

    Post deleted.
    Last edited by kvsrinivasamurthy; 11-08-2019 at 12:27 AM.

+ 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. [SOLVED] How do i get a sum of monthly headcount using multiple criteria
    By mallory.chui in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 10-13-2015, 04:22 AM
  2. Monthly Headcount Change Stacked Column Waterfall
    By scampy74 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 04-15-2015, 03:56 PM
  3. automatically calculate my WEEKLY and MONTHLY totals on a spreadsheet
    By drobinson782001 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-15-2013, 06:32 PM
  4. Replies: 1
    Last Post: 07-17-2013, 11:34 AM
  5. Replies: 1
    Last Post: 05-18-2011, 10:46 PM
  6. Calculate time and salaries
    By [email protected] in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-22-2007, 07:40 AM
  7. Replies: 0
    Last Post: 03-03-2006, 06:30 AM

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