+ Reply to Thread
Results 1 to 3 of 3

Date format problem using a formula in VBA to calculate difference between times and dates

  1. #1
    Registered User
    Join Date
    10-01-2012
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    3

    Unhappy Date format problem using a formula in VBA to calculate difference between times and dates

    I have been using the formula in quotes below in (courtesy of DaddyLongLegs - thank you!) to calculate the difference between two columns of dates and times incorporating weekends and holidays, within a macro in VBA.

    I start off with the date and time in separate columns (formatted as dd/mm/yy) and combine them using a formula (e.g. A1+B1). However, as I understand that VBA works with dates in American format I am finding that for dates below the 13th of the month they are changing to mm/dd/yy hh:mm when they are combined in the same cell. This is affecting subsequent calculations that are held within the macro as when I use the formula below, the dates appear to be being treated as UK format.

    e.g. the difference between 03/07/12 and 09/07/12 becomes 6 months! (changes to 07/03/12 and 07/09/12).

    I have tried changing all dates to American format in the first place but I'm still having the same problem.

    I know that date format is a common limitation with VBA but haven't been able to find a solution/workaround that I can understand despite hours of trawling forums - I have seen some that mention using the regional settings for the format but couldn't figure it out - please bear with me as I am completely self-taught and am learning as I go!

    Any help would be much appreciated.

    "Assuming the following cell references

    A2 = start date/time
    B2 = end date/time

    D2 = weekday start time (08:00)
    E2 = weekday end time (17:00)

    holidays F2:F28

    If you want a formula which accomodates start or end dates which might be Sats, Suns or holidays, i.e. start and end times can be anything then use

    =(NETWORKDAYS(A2,B2,F$2:F$28)-1)*(E$2-D$2)+IF(NETWORKDAYS(B2,B2,F$2:F$28),MEDIAN(MOD(B2,1),D$2,E$2),E$2)-MEDIAN(NETWORKDAYS(A2,A2,F$2:F$28)*MOD(A2,1),D$2,E$2)"
    Attached Files Attached Files

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Date format problem using a formula in VBA to calculate difference between times and d

    Don't know if this helps with the specific problem but you can adapt my suggested formula to work directly with dates/times in separate cells (in fact it's a slightly simpler formula that way). For your setup that would require this formula in F2 copied down

    =(NETWORKDAYS(B2,D2,I$2:I$28)-1)*(H$2-G$2)+IF(NETWORKDAYS(D2,D2,I$2:I$28),MEDIAN(E2,G$2,H$2),H$2)-MEDIAN(NETWORKDAYS(B2,B2,I$2:I$28)*C2,G$2,H$2)
    Audere est facere

  3. #3
    Registered User
    Join Date
    10-01-2012
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Date format problem using a formula in VBA to calculate difference between times and d

    Thanks - I will give it a go for this and certainly use it in future; after messing around with bits of the macro (for hours!) I found out that it was a bit of code I was using to find and replace text that was causing the problem. Very odd as it was seemingly unrelated to anything to do with dates!
    Thanks for the reply though.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1