+ Reply to Thread
Results 1 to 9 of 9

how to adjust salary advances of different months against last unpaid month salary

  1. #1
    Registered User
    Join Date
    09-04-2013
    Location
    Nigeria
    MS-Off Ver
    Excel 2007
    Posts
    5

    Post how to adjust salary advances of different months against last unpaid month salary

    Dear,

    Sorry if i can't explain it well. I am working on one excel sheet where i have following column,
    gross salary deduction of advances amount due amount paid balance payable

    i have more than one advances for each person in different months and i want to set them off against their last unpaid salary. e.g. the advance taken in sep,2013, oct, 2013 is now i have to adjust against january 2013 salary. (company pay salaries in arrears). please is some one can assist me in this. and salaries are being paid in different currencies.

    thanks

    RAJA
    Attached Files Attached Files
    Last edited by sadaqat_ssa; 09-04-2013 at 02:28 PM. Reason: attachement

  2. #2
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: how to adjust salary advances of different months against last unpaid month salary

    Hello and welcome.
    Can you state your expected result?
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk

  3. #3
    Registered User
    Join Date
    09-04-2013
    Location
    Nigeria
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: how to adjust salary advances of different months against last unpaid month salary

    Quote Originally Posted by RobertMika View Post
    Hello and welcome.
    Can you state your expected result?
    thanks Robert!

    I am going to draw advances table down to my attached sheet where i am going to put all my advances paid on different dates and their month to be recovered. the table will be:
    date description amount paid as advance recovery month
    2.2.2013 advance against jan 13 $ 2,000 January 2013
    12.2.2013 advance against jan 13 $ 1,000 January 2013
    22.3.2013 advance against jan 13 $ 600 February 2013
    29.4.2013 advance against feb 13 $ 1,000 February 2013
    23.5.2013 advance against feb 13 $ 1,660 March 2013
    3.7.2013 advance against mar 13 $ 2,500 April 2013

    The company policy is stating that "any advance paid will be recovered from last outstanding salary". so here the last unpaid salary is January 2013. Now i want the sum of advances to be adjusted against January 2013 salary under advance deduction column but it should not exceed the balance salary amount of that month. And in case if advance amount is more than the outstanding of that month then it should go to next month advance deduction.
    please feel free to ask if any confusion.
    thanks

  4. #4
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: how to adjust salary advances of different months against last unpaid month salary

    Could you state within your workbook your desired result?

  5. #5
    Registered User
    Join Date
    09-04-2013
    Location
    Nigeria
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: how to adjust salary advances of different months against last unpaid month salary

    Quote Originally Posted by RobertMika View Post
    Could you state within your workbook your desired result?
    I want to get a formula which can give me total of all advances paid in any month but to be deducted in Jan 2013 while keeping in mind that the deduction should not increase than salary payable for that month if so, the excess amount should go to February Salary ans so on.e.g from my excel sheet now

    In cell "I8" I want to get sum of all advances from column "I27:I55" deductable against month selected in cell "A27:A55" not more than column "P8". And if it Is more than column "P8" then excess amount should go to next month means cell "I9" and so on.

    new file is here

    thanks robert
    Attached Files Attached Files

  6. #6
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: how to adjust salary advances of different months against last unpaid month salary

    I repeat .
    State your numbers within workbook.

  7. #7
    Registered User
    Join Date
    09-04-2013
    Location
    Nigeria
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: how to adjust salary advances of different months against last unpaid month salary

    Quote Originally Posted by RobertMika View Post
    I repeat .
    State your numbers within workbook.
    sorry robert!

    please find attached results.

    thanks
    Attached Files Attached Files

  8. #8
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: how to adjust salary advances of different months against last unpaid month salary

    I4= 4000
    P8 IS 0
    (BTW P8 is a cell not column)

    The SUM for JANuary where there is Advance in column C from cells I27:i55 is 2500

    How this add up?

  9. #9
    Registered User
    Join Date
    09-04-2013
    Location
    Nigeria
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: how to adjust salary advances of different months against last unpaid month salary

    i did it manually here i want formula to get this result. p8 became 0 because of I8 entry. What i want is this cell (I4) formula should automatically check what figure is in cell p4 and according to that figure it should take amount of total advance for certain month. Now total of January advance is not 2500 it is 5000, so i took 4000 in january and remaining 1000 under february+february own.

    hope you understand now

+ 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. FUTA tax by month with salary cap
    By Aguhr in forum Excel General
    Replies: 1
    Last Post: 02-07-2017, 10:42 PM
  2. Net to gross salary
    By TK34 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-16-2013, 08:58 AM
  3. Replies: 3
    Last Post: 07-26-2012, 04:11 PM
  4. Excel 2007 : Salary scheme
    By Skjo in forum Excel General
    Replies: 1
    Last Post: 05-22-2011, 01:02 AM
  5. Salary sheet
    By irsath in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-29-2007, 08: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