Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2015

    Unanswered: Calculating Date Difference in Workdays

    I am using the Workday function created in VBA provided from this link here:

    However, the problem I am having is that I want it to return true difference between (excluding weekends and holidays) the two dates in decimal format.


    startdate = 01/05/2015 12:00:00 PM
    enddate = 01/12/2015 11:00:00 AM

    Should return a value of 4.9583333 instead of 6.

    Many thanks in advance.


  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    so extend the function.
    you need to know what the length of a standard working day is
    and then add the proportion of the part whole days (the start and end datetimes from that.

    so the psuedo code would be something like:-

    workout the number of time units in a working day (id probably do that as seconds
    work out how many of those time units you've worked on the starting day
    work out how many of those time units you've worked on the ending day
    add this values together, convert it into required time units

    I'd implement the working it number of units worked as a function and call it twice (once for start and once for finish). I'd suggest you supply the cutoff time (of either start or finish working) as part of the fucntion call so you can re use the code with different values

    if you supply say the start of the working day then you maths is fairly simple
    nosecondsinworkingday = 8 * 60 * 60 'assumign an 8 hour working day converted to seconds
    workingtime = nosecondsinworkingday - calcnosecondsworked(startofworktime,format(startda te,"HH:MMS")
    workingtime = workingtime + calcnosecondsworked(startofworktime,format(enddate ,"HH:MMS")
    workingtime = workingtime / nosecondsinworkingday
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts