Calcuating time difference (Word 97/VBA)

Topic

New Reply

I’m having a problem calculating times using DateDiff. My project is an employee timesheet. For example, the user will enter the time they arrived in the morning and when they left for lunch. I need to calculate the difference between these two. Not a problem if they leave at noon, but if they arrive at 8:00 a.m. and leave at 1:00 p.m., I get a negative value. I know I’m omitting something necessary…perhaps a lot of things, since I’m pretty much a novice in this area.

Here’s what I wrote:

ActiveDocument.Variables.Add Name:=”MTotal”, Value:=Format(DateDiff(“h”, TB3, TB4), “HH:mm”)

I assume I would first have to determine if TB3 is am or pm; likewise for TB4.

Any assistance would be greatly appreciated.

Reply | Quote

Replies

You’re on the right track in choosing DateDiff. Ideally, the two date arguments supplied to DateDiff should be of the Date data type, but reasonable conversions will be carried out for you. Try some experiments in VBA’s immediate window along the following lines: (At the risk of teaching you to suck eggs, you only need to type or copy the lines starting “?” – when you hit the return key, the value of the expression after “?” will be displayed. The stuff after ‘ — below is just my comments.)

?DateDiff(“h”, “8:00”, “20:00”)
12
?DateDiff(“h”, “8am”, “8pm”)
12
‘ — so far this makes sense, but look at these two:
?DateDiff(“h”, “8:01”, “20:59”)
12
?DateDiff(“h”, “8:59”, “20:01”)
12
‘ — the minutes are IGNORED if you use “h”. So let’s use “n” instead,
‘ (“m” means months, so minutes are indicated by “n”)
?DateDiff(“n”, “8am”, “8pm”)
720
?DateDiff(“n”, “8:01”, “20:59”)
778
?DateDiff(“n”, “8:59”, “20:01”)
662
‘ — the 116 minute difference between these two durations is
‘ reflected correctly this time.

‘– now, to get the interval formatted nicely instead of working out how many
‘hours and minutes there are in 662 minutes, remember that the Date datatype
‘is really an integer indicating a date, plus a number between 0 and 1 indicating
‘the time of day on that date. So divide the time span in minutes by the number
of minutes in a day, and format it:

?Format(DateDiff(“n”, “8:59”, “20:01″)/(24*60),”hh:nn”)
11:02
‘ — Job done

If you double-click “DateDiff” in the immediate window and hit F1, you’ll get the official detail on how this function works.

You may need to read the small print about how DateDiff reacts to Regional Settings if your solution needs to work in a wide variety of countries.

Reply | Quote

Wow! That’s awesome… I’ll give it a shot. It certainly seems to make sense to me at first glance. Thank you so much for taking the time to reply. I’ll let you know how it works out. Much appreciation

Reply | Quote