+ Reply to Thread
Results 1 to 5 of 5

Subtract dates and times for overtime ...and also consider weekends and holidays

  1. 01-14-2019, 11:55 AM #1
    blacktie
    blacktie is offline
    Registered User
    Join Date
    01-27-2013
    Location
    SA
    MS-Off Ver
    Excel 2010
    Posts
    37

    Subtract dates and times for overtime ...and also consider weekends and holidays

    PLEASE PLEASE HELP

    I need help with a formula to deduct ie dates and times to calculate regular time and overtime and multiply the regular time with the basic salary of R18.75 (Rand)....and Saturdays with Basic x1.5 ...and Sundays & public Holidays with basic salary x2 (The Holidays for 2020 I will past into the same space where the current holidays for 2019 are situated - =U9:U22


    The colum J must calculate regular hours and colums K calculate Overtime hours for that specific row

    What makes this difficult is that we sometimes work for 26 hours on end thus regular time & overtime spans over 2 days to be calculated and that is what I'm battling with
    I have manually calculated the regular hours (J3:J6) and the overtime hours (K3:K6) as a reference for your perusal

    I've attached Example.xlsx file

    I would so appreciate it if an Excel genius can assist me in this rtegard

    Kind regards
    Willie
    Attached Files Attached Files
    Last edited by blacktie; 01-14-2019 at 12:11 PM. Reason: forgot to attach Example.xlsx file
    Reply With QuoteRegister To Reply
  2. 01-14-2019, 01:08 PM #2
    XLent
    XLent is offline
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Subtract dates and times for overtime ...and also consider weekends and holidays

    Using the sample file, perhaps:

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


    note: I think first OT value should be 36 hours, rather than 37?

    On an aside, I would think you actually need to split your OT out such that you can distinguish between Mon-Sat OT (x1.5), and Sun/Public Holidays OT (x2)
    Reply With QuoteRegister To Reply
  3. 01-14-2019, 01:23 PM #3
    blacktie
    blacktie is offline
    Registered User
    Join Date
    01-27-2013
    Location
    SA
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Subtract dates and times for overtime ...and also consider weekends and holidays

    Mr Xlent

    Thank you so much for taking the time to help a stranger in dire need - I really appreciate it

    I have entered your code into my example.xlsx file but unfortunately both J3 and K3 return with a 00:00 value

    Would you please be so kind to re-check for me - please great sir
    Reply With QuoteRegister To Reply
  4. 01-14-2019, 01:25 PM #4
    XLent
    XLent is offline
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Subtract dates and times for overtime ...and also consider weekends and holidays

    format cells as General or Number (to say 1 or 2 decimal places)
    Reply With QuoteRegister To Reply
  5. 01-14-2019, 01:33 PM #5
    blacktie
    blacktie is offline
    Registered User
    Join Date
    01-27-2013
    Location
    SA
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Subtract dates and times for overtime ...and also consider weekends and holidays

    Mr XLent

    This works 100%...!!! You are my ABSOLUTE HERO !!

    Thank You sooooo much......This so highly appreciated !!!!


    Kind regards Willie
    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. Subtract two times (incl. holidays and excl. weekends)
    By blens1 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-09-2015, 06:59 PM
  2. [SOLVED] Calculating handling times between two dates, in hours, excluding holidays/weekends
    By Tunesmith in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-17-2015, 01:44 PM
  3. hour difference between two dates/times, exclude weekends and holidays
    By lamdl in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-23-2014, 09:11 PM
  4. Calculate Working Days (excluding weekends & holidays) between two dates/times
    By bool in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-19-2013, 05:45 AM
  5. [SOLVED] Calulating Time Between Two Dates/Times but Excluding Weekends and Holidays
    By randers1984 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-15-2013, 10:24 AM
  6. Need to subtract two dates/times taking into account holidays, weekends.
    By chauhan101 in forum Excel General
    Replies: 5
    Last Post: 05-18-2012, 10:24 AM
  7. Subtract two dates (not considering weekends and holidays)
    By Kinna in forum Excel General
    Replies: 1
    Last Post: 06-04-2009, 03:43 PM

Tags for this Thread

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