asked on

Difference between 2 dates in Months, Days, Hours, Minutes

Hi everyone. I have looked through previously answered questions but I can't find a solution for what I need to accomplish and I am hoping that someone can help.
I am trying to create metrics reports that contain the date that a request was entered, the date that the request was completed and the difference between the 2 dates with the results in Months, Days, Hours, Minutes format. The difference needs to be displayed in one column and in another column I need the difference between the 2 dates - not including weekends and holidays. For example: if a request is submitted on Friday morning at 4:30pm and completed on Monday at 10am, I do not want the difference in the second column to show as 2 + days.
Is this possible?
Thanks,
Josette

Kevin Pham

Not in my knowledge that DATEDIFF function will take care of the weekend days for you... but I can't see why not to use an application function to further disect the dates and calculate the difference.. here's a pseudo idea:

count=0
for i = convert_to_integer(startdate) to convert_to_integer(enddate)
   if day_of_week(i) is NOT weekend
        count++;
   end if
next

Open in new window

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kevin Pham

I wanna change the above code a bit

count=0
for i = convert_to_integer(startdate) to convert_to_integer(enddate)
   if day_of_week(convert_to_date(i)) is NOT weekend
        count++;
   end if
next

Open in new window

peter57r

Using Months in such a measurement is totally meaningless, especially when you are excludiing weekends and holidays.
Stick to days.

jkoneil

ASKER

How do I get the Interval referenced in the function?
Is it just Datediff(DateCompleted-DateRequested)?
Thanks.
Josette

Rey Obrero (Capricorn1)

if you will refer to the link..
it should be

ElapsedTime(#6/1/1999 8:23:00PM#-#6/1/1999 8:12:12AM#)

jkoneil

ASKER

Is there a way for me to split this question so that I can award points individually? I was able to get the first part working using the function from capricorn1 but I am still working on calculating without the weekends.
Thanks,
Josette

Rey Obrero (Capricorn1)

just add this test

if DateDiff("ww", DateSerial(Year(dateRequest), Month(dateRequest), 1), dateRequest, vbSunday) + 1 = DateDiff("ww", DateSerial(Year(dateCompleted), Month(dateCompleted), 1), dateCompleted, vbSunday) + 1 then

' do nothing same week
else

'subract two days or 48 hours

end if

jkoneil

ASKER

Since I need a column for the amount of time including the weekend and a column for the amount of time not including the weekend, I added a new function with the following:

else
ElapsedTimeNoWeekend = DateAdd("d", -2, x)

This did not work. I receive a # error when I run the query using this function.

Also, does the check assume that there can only be one weekend between the request and the completion? We have several cases where there are weeks between the request and the completion.

Thanks for your help.

Josette

Rey Obrero (Capricorn1)

{ We have several cases where there are weeks between the request and the completion. }
now, a user define function is what you need, please post all the possible scenarios

SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jkoneil

ASKER

Thanks for the function Gustav. I was able to use it to determine the minutes. I haven't been able to convert the minutes to days, hours and minutes yet.

Can I repost the Working Days portion of the questions so that I can award full points for you and capricorn1 for the ElapsedTime function?

Gustav Brock

Thanks, but a split is fine with me.

You can extract the day, hour, and minute count by using Modulus and integer division:

intMinutes = lngMinutes Mod 60
intHours = lngMinutes \ 60 Mod 24
intDays = lngMinutes \ 60 \ 24

/gustav

jkoneil

ASKER

Thank you for all of your help. The metrics queries seem to be running fine.

Gustav Brock

You are welcome!

/gustav