+ Reply to Thread
Results 1 to 3 of 3

Leave Allowance Calculation

  1. #1
    Registered User
    Join Date
    06-17-2015
    Location
    singapore
    MS-Off Ver
    2010
    Posts
    43

    Leave Allowance Calculation

    I would like to ask for someone advise who can help .

    I am trying to set a leave formula as below. Total the whole year , leave allowance is 14 days.
    But, it will be allocated by current month. If let's say, current month is *Jan* , then (14 days / 12 months )* month 1) , if Feb, if march , etc..

    My below IF formula is working what I want. But, is there any better formula than this ?

    IF(MONTH(TODAY())=1,(14/12),IF(MONTH(TODAY())=2,(14/12)*2,IF(MONTH(TODAY())=3,(14/12)*3,(IF(MONTH(TODAY())=4,(14/12)*4,(IF(MONTH(TODAY())=5,(14/12)*5,(IF(MONTH(TODAY())=6,(14/12)*6,(IF(MONTH(TODAY())=7,(14/12)*7,(IF(MONTH(TODAY())=8,(14/12)*8,(IF(MONTH(TODAY())=9,(14/12)*9,(IF(MONTH(TODAY())=10,(14/12)*10,(IF(MONTH(TODAY())=11,(14/12)*11,(IF(MONTH(TODAY())=12,(14/12)*12,"TO CHECK")))))))))))))))))))))

    Thanks in advance

  2. #2
    Forum Contributor codeslizer's Avatar
    Join Date
    05-28-2013
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2003 - 2010
    Posts
    245

    Re: Leave Allowance Calculation

    Well, why don't you simply replace numbers (i.e. instead of writing 1, 2, 3 and so on) with the MONTH(TODAY()).. so your formula would come down to

    Please Login or Register  to view this content.
    This formula won't give you an error, so no need for any checks.
    cOdEsLiZeR - Back after a long break.. Let's sLiZe some more cOdEs!!

  3. #3
    Registered User
    Join Date
    06-17-2015
    Location
    singapore
    MS-Off Ver
    2010
    Posts
    43

    Re: Leave Allowance Calculation

    Hi Codeslizer,

    Thanks for your simple shortcut . Thanks again
    Last edited by PMH2384; 05-12-2016 at 09:10 PM.

+ 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. Daily Allowance Calculation From Previous Daily Spending Against A Daily Budget
    By WillYoung351 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-03-2014, 05:00 PM
  2. Calculation of shift allowance affected by overtime and overnight shifts
    By danb1985 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-09-2014, 06:16 AM
  3. [SOLVED] Calculation of Night Allowance between specific hours using excel
    By Womble2011 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-04-2012, 03:49 AM
  4. [SOLVED] Calculation of annual leave
    By youngjeesu in forum Excel General
    Replies: 3
    Last Post: 08-10-2012, 01:08 AM
  5. Uniform Allowance - Formula for Totals in Excess of Allowance
    By kgreene82 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-15-2012, 05:31 PM
  6. Leave #N/A blank until a calculation appears
    By kukarooza in forum Excel General
    Replies: 6
    Last Post: 08-17-2011, 02:05 PM
  7. Annual Leave Calculation Problem
    By pforparis in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-02-2010, 07: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