VBA Hour Calculation Between 2 dates/times

vbaNewbieMan,

Does this have to be VBA? I'm pretty sure you could do this with just a formula. And so that I'm understanding you right, given this start date/time and end date/time, is the result correct?

Start Date/Time	End Date/Time	Total Hours
6/7/2013 5:50:00	6/11/2013 15:50:00	34:50:00

So the full 13 hours were worked on 6/7, which is a Friday, because the start time of 5:50 am is prior to the work hours start time of 7 am
6/8 and 6/9 both count as 0 because that's a weekend
The full 13 hours were worked on 6/10
15:50 is 3:50 pm, so 8 hours and 50 minutes were counted for 6/11

That leaves us with 13+0+0+13+8:50 = 34:50
If that is correct, this formula should do the trick:

Please Login or Register  to view this content.

[EDIT]
Alternate formula:

Please Login or Register  to view this content.

Unfortunately, that formula does not work. I have an open date of (6/3/13 16:28) and a close date of (6/3/16:40) and the return hours passed in the column comes out as (1/1/1900 15:12). The only part(s) that are correct are the days (1/1/1900) and the minutes of the time (:12).

If it is at all possible, I would love it if the days could be dropped and turn into hours, so that (1/1/1900)=0 hours and (1/2/1900)=13 hours. I also have no idea where the hours of (15) came up in the (15:12).

Thank you so much for your help tigeravatar.

EDIT: Sorry, I forgot to mention that I used the first formula, as the second one does not seem to want to work.

vbaNewbieMan,

Alright, it's not the prettiest formula in the world, but this should do it:

Please Login or Register  to view this content.

Also, format the cell with a custom format of [h]:mm

At this point I'd have to see a sample file with expected results

Email Received ||| Date Entered ||| Time Elapsed

6/7/2013 17:50 ||| 6/7/2013 18:22 ||| 0:32 <--------- what I would like

6/7/2013 17:10 ||| 6/7/2013 18:08 ||| 1/1/1900 15:58 <--------- what I get, and sometimes it gets messier

6/5/2013 10:12 ||| 6/7/2013 10:15 ||| 1/1/1900 2:03 <----------- Messy version where I don’t know how it got that.

vbaNewbieMan,

Attached is an example workbook using the sample data you provided in the PM you sent me. As far as I can tell, the formula is working properly and returning the correct number of hours for every example (which I verified by hand). You just need to change the formatting to Custom with a format of [h]:mm

I am using 2007 currently, but the compatibility checker didn't give me any problems...
I don't have access to my 2003 copy of Excel at the moment. I'll see if I can get someone to look at it, and there's also probably a better formula that can used too

vbaNewbieMan,

Here is a version that uses the Weekday() function instead of the Networkday() function.
Formula in P2 and copied down:

Please Login or Register  to view this content.

in 2003 you need the analysis toolpak add-in installed to use NETWORKDAYS

Now that's praise worth a Batman visual.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Thanks.

If start and end times are always within the working hours then this formula should give you the correct hours using NETWORKDAYS

=(NETWORKDAYS(E2,F2)-1)*("20:00"-"7:00")+MOD(F2,1)-MOD(E2,1)

or if you want to avoid NETWORKDAYS function this will give the same result

=(SUM(INT((WEEKDAY(E2-{2,3,4,5,6})+INT(F2)-INT(E2))/7))-1)*("20:00"-"7:00")+MOD(F2,1)-MOD(E2,1)

Either way format result cell as [h]:mm