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)"
Bookmarks