Time Calculations using VBA.

[Guest]

Hi Excel Gurus,

I want to use Excel VBA to calculate daily working time. Both Start time and End time have to go by quarters of hours. It means that Starting at 09:05 is equivalent to 09:00. at 09:08 will be 09:15 etc.
Since I'm a novice to Excel, I have some difficulties to add/subtract minutes from Time().
Then I have to subtract one (1) hour from the total time, for lunch break.

How do I manipulate times in Excel?

Thanks
Eric

 

[Kieran]

Dates and times are stored in excel as a number, starting at 1/1/1900 as
) and incrementing by 1 each day.

Therefore an hour is = 1/24 or 0.041666667 of a day.

Today 5/1/2004 is 37991.


As time is a number you can simply adda nd subtract them as for any
other number.

Please note however that when Excel dispays a number, it assumes by
defult that the time is to be showing as a portion of 24 hours. So 32
hours(or 1.333333333 as a numebr) is displayed as 8:00 not 32:00.

To display the time in hours for periods greater than 24, amend the
cell custom format to read [h]:mm. ( the usual cell format is h:mm).
The [] instruct eexel to show the full hours.

Hopefully that can get you started with times.

 

[Guest]

Thanks Kieran and Rob.

Rob you saved me the main part of rounding the time. Do you know how to subtract 1 hour for lunch from the time (without it I cannot take my lunch break)?

And again thanks to you guys.

 

[Rob van Gelder]

Eric,

If I'm assuming the non-complicated solution, then you just subtract 1 hour
from the duration:

Sub testit()
Dim dtmStart As Date, dtmEnd As Date, dblDuration As Double

dtmStart = "1-Jan-2004 09:05"
dtmEnd = "1-Jan-2004 17:19"

dtmStart = Round(dtmStart * 24 * 4) / 4 / 24
dtmEnd = Round(dtmEnd * 24 * 4) / 4 / 24

dblDuration = dtmEnd - dtmStart - 1 / 24

MsgBox Format(dblDuration, "hh:mm")
End Sub


I like to think of a date datatype as just a number in disguise.


Rob

Thanks Kieran and Rob.

Rob you saved me the main part of rounding the time. Do you know how to

subtract 1 hour for lunch from the time (without it I cannot take my lunch
break)?

 

[Rob van Gelder]

Eric,

Sub testit()
Dim dtmStart As Date

dtmStart = "1-Jan-2004 09:05"
dtmStart = Round(dtmStart * 24 * 4) / 4 / 24
End Sub


Rob

Hi Excel Gurus,

I want to use Excel VBA to calculate daily working time. Both Start time

and End time have to go by quarters of hours. It means that Starting at
09:05 is equivalent to 09:00. at 09:08 will be 09:15 etc.

 

[Clay Watson]

Hello, Rob van Gelder and any other VBA afficionados out there! That
was a clever solution to the date/time workday calculation question you
provided. Could I ask about a somewhat different scenario?

Instead of Excel, my situation is in Access. I have two date/time
fields, one for Start and one for Finish. I am trying to calculate net
workday work hours. If a trouble ticket is not resolved (Finish) on the
same day, I need to calculate how many 8a-5p Mon-Fri workday hours
elapsed.

Looks like Excel has a NetWorkday function. Would it be easiest for me
to import my Access data into Excel, and attempt to use Excel? THANK
YOU.
..Clay

 

[Rob van Gelder]

I don't think I ever got to writing the solution. Just managed to describe
the solution.
As I recall, it was for ambulance resource management. Norman Harker
provided the solution offline. Perhaps, if he's listening, he can provide
you the quick answer?

I will be posting a solution within the next few days and if I ever get
around to putting a website up.... well...

The logic goes a little like this:
Think about the 4 events. A, B, C, D.
A = Start Time
B = End Time
C = Interval Start
D = Interval End

Figure out the permutations (I found John Walkenbach's VBA routine handy for
this: http://www.j-walk.com/ss/excel/tips/tip46.htm)

You'll get 24 combinations, but assuming C always occurs before D then 12 of
those can be eliminated.

It's strange to think that B could occur before A, but times don't have a
date. So while it's possible a troubleticket might have been (A, B) from
1-Jan-2004 14:00 to 3-Jan-2003 09:00, the times we compare are without the
date.


Stay tuned.

Rob

 

[wssparky]

Eric,
I'm working on the same sort of project. May I ask if you got it to
work could I get a look at the solutions you came up with ?
This time thing is really getting to me.

wssparky______________________

We learn by doing, and doing ……and doing ……