Add minutes to a time calculation

rmccafferty · September 27th, 2009, 03:07 PM

I have a time calculation (end time minus start time) that is not working quite right. It is used in time logs to pay people.

If a time entry is 8:00am - 11:00am, it will return 3 hours.

But if it is multiple entries, the people type entries like the following:
8:00am - 9:15am
9:16am - 10:15am
10:16am - 11:00am
It will return something less than 3 hours even though contractors believe that all minutes are seemingly accounted for.

I would like to add 1 minute to each calculation and do not know how to add minutes in time in a formula.

The code I now use in a macro is: (This is part of a loop)
Dim lLastGRow As Long
lLastGRow = ActiveSheet.Cells(Rows.Count, "G").End(xlUp).Row
Range("H2:H" & lLastGRow).FormulaR1C1 = "=(RC[-1]-RC[-2])*24"

Essentially, the cursor is in the column that needs the total and it subtracts the entry 2 columns to the left from the entry 1 column to the left. Bot of those cells are in a time format. I want to just add 1 minute to each calculation. (Yes, it will ultimately add a few minutes to people's pay, but overall the amount is meaningless; what I cannot do is underpay by a minute.)

How do I add that 1 minute in the formula? I cannot just add +1, I cannot just add +:01. I do not want to put 1 minute in a cell somewhere and always add in that cell. There must be an easier way. Actually, I guess I would not know quite how to put a 1 minute entry in a cell and add it either.

Help?

Shasur · September 28th, 2009, 10:04 AM

Hi

Can you try the following in VBA

dateadd("n",1,now)

Cheers
Shasur

allenm · September 28th, 2009, 10:04 AM

Time literals in VBA are enclosed with # signs and must be appropriately stated. #12:00 AM# is the same as adding 0 to whatever time you already have so try adding as such:

Code:

Dim dTime As Date
dTime =Now()
msgbox dTime & " + 1 Minute is " & dTime + #12:01 AM#

Hope this helped point in the right direction.