+ Reply to Thread
Results 1 to 5 of 5

Help piecing together with formulas

  1. #1
    Registered User
    Join Date
    12-26-2016
    Location
    Hong Kong
    MS-Off Ver
    Excel
    Posts
    32

    Help piecing together with formulas

    Hi Excel Experts, can anyone help me piece this together.I have made it up so far but am a complete novice at formulas. I would love for it to be able to automatically accrue annual leave and statutory holidays using a Today date function? Statutory holidays are accrued and taken at a later date ( Food & Beverage business)
    Total accrual from start date would be fantastic but maybe asking too much there.

    I think i have attached the file.

    You time and experience much appreciated

    Donald
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Help piecing together with formulas

    Can you add sample results to you file:

    For "SH Accrued"

    =SUMPRODUCT((Sheet1!$B$4:$B$16>=E$1)*(Sheet1!$B$4:$B$16<=EOMONTH(E$1,0)))

    Change any dates to Excel date form (01/01/2017) and then format as required.

    See attached (add results to this please).
    Attached Files Attached Files

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Help piecing together with formulas

    Another way to do that...

    =COUNTIFS(Sheet1!$B$4:$B$16,">="&E$1,Sheet1!$B$4:$B$16,"<="&EOMONTH(E$1,0))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    12-26-2016
    Location
    Hong Kong
    MS-Off Ver
    Excel
    Posts
    32

    Re: Help piecing together with formulas

    Hi,
    Thanks for the replies. I have attached an example for January.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Help piecing together with formulas

    Try

    S/H accrued: Checks if date <= Month and <=TODAY

    =SUMPRODUCT((Sheet1!$B$4:$B$16>=E$1)*(Sheet1!$B$4:$B$16<=EOMONTH(E$1,0)*(Sheet1!$B$4:$B$16<=TODAY())))

    A/L accrued to date

    =VLOOKUP($B3,Sheet1!$K$5:$M$15,3,0)*MONTH(EOMONTH(TODAY(),-1))

    S/H Accrued to date

    =SUMIF($E$2:$AB$2,"S/H accrued",$E3:$AB3)

    Total Taken

    =SUMIF($E$2:$AB$2,"Holidays Taken",$E3:$AB3)

    Total available

    =VLOOKUP($B3,Sheet1!$K$5:$M$15,2,0)-$AE3+$AC3


    As before changed ALL dates to dates (01/01/2017): please keep dates in this form

    Add February Statuatory Holiday to test calculation of "S/H accrued"

    Changed "Accrued PTO table": removed extraneous column and text.
    Attached Files Attached Files

+ 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. Replies: 3
    Last Post: 01-01-2016, 03:44 PM
  2. Replies: 9
    Last Post: 12-04-2015, 05:19 PM
  3. Poll on - Lots of simple formulas vs fewer but more complex formulas
    By gassiusmax in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-20-2014, 08:51 PM
  4. Using Cell references in file paths for formulas to create dynamic formulas
    By MichaelStokesJr in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-04-2013, 11:49 AM
  5. Replies: 5
    Last Post: 09-25-2013, 02:51 PM
  6. piecing together cell formula from different cells
    By umbata in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-22-2013, 01:13 PM
  7. Replies: 5
    Last Post: 05-05-2008, 02:22 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