+ Reply to Thread
Results 1 to 4 of 4

Leave Calculation working days and exclude sunday

  1. 03-24-2023, 01:37 AM #1
    webmax
    webmax is offline
    Registered User
    Join Date
    07-08-2012
    Location
    chennai
    MS-Off Ver
    Excel 2019
    Posts
    54

    Leave Calculation working days and exclude sunday

    Hi,

    I have a data which contains from date and to date. I want to calculate the leave days through formula.

    In addition to that there is a condition for Saturday ie 1st and 2nd Saturday 0.5 day working, 3rd Saturday is a holiday, 4th Saturday full working day.

    Example if the person took leave between 1st Mar 23 to 4th Mar 23, then the leave days is coming as 3.5 days ( 1st Mar -1 day, 2nd Mar -1 day, 3rd Mar -1 day and 4th Mar is a 1st Saturday so calculate as 0.5 days). If person took leave between friday to monday then the sunday should not be counted.

    Attaching the sample excel sheet with output data mention in the D Column. Need formula in E Column.

    Thanks
    Webmax
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-24-2023, 02:45 AM #2
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,481

    Re: Leave Calculation working days and exclude sunday

    Generate a helper column K:M, to list down from 1st to 28/29/30/31 each month
    Some month 31 days, with Saturday fall into 1st will have 5 Sats. How about 5th Sats?
    Formula in M2:
    =IFERROR(AGGREGATE(15,6,{0,0.5,0.5,0,1,1}/(COUNTIF($L$2:L2,7)={0,1,2,3,4,5})/(L2=7),1),"")
    assum Sat 0 to 5 is add {0.5,0.5,0,1,1}. Change the 5th Sat (=1) to 0 if it was ({0,0.5,0.5,0,1,0}).
    Attached Files Attached Files
    Last edited by bebo021999; 03-24-2023 at 02:47 AM.
    Quang PT
    Reply With QuoteRegister To Reply
  3. 03-24-2023, 03:53 AM #3
    JohnTopley
    JohnTopley is offline
    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,369

    Re: Leave Calculation working days and exclude sunday

    What about holidays over a month-end e.g 28/03/2023 to 10/04/2023 ?
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Reply With QuoteRegister To Reply
  4. 03-24-2023, 06:20 AM #4
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,628

    Re: Leave Calculation working days and exclude sunday

    In E2 copy down.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    Mark leave calculation given is wrong.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 03-24-2023 at 06:30 AM.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Leave days calculation
    By skml in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-27-2023, 07:17 AM
  2. Exclude Sunday while adding days to a given date
    By msantosh1220 in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 03-24-2019, 01:22 AM
  3. Scheduling in Excel (How to exclude non-working time in the calculation)
    By mbrillian in forum Excel General
    Replies: 6
    Last Post: 09-06-2018, 09:28 AM
  4. Working days and exclude bank holidays
    By K-Linerz in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-03-2018, 10:25 AM
  5. [SOLVED] Count 6 working days (excluding Sunday, Holidays & half days)
    By eve_star1 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-29-2015, 08:00 AM
  6. Calculate total working days and excepted leave days
    By megaiooo in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-21-2013, 09:29 AM
  7. If statement to include working days, exclude holidays
    By rhudgins in forum Excel General
    Replies: 2
    Last Post: 05-27-2010, 09:46 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1