Excel Add minutes to a time calculation in Excel

[rmccafferty]

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?

 

[JaLing]

Your issue is due to a misunderstanding of arithmatic with time.
Example...
You worked 1hour - 11.00 to 12.00
but stopped for 0 seconds at 11.30
So I would say you worked
11.00 to 11.30 and then 11.30 to 12.00 = 60mins
but you're saying you worked
11.00 to 11.30 and then 11.31 to 12.00 = 59mins

Effectively your entering the first completed minute of work as when they started.

I could give you a formula to add a minute but when finances are involved you don't bodge it as over a year those extra minutes will add up.

 

[rmccafferty]

Well, what I really need is a formula to add a minute that will work in VBA in a macro (with the existing code listed above). So many people want to tell me what I should not do and apparently most of them do not really know how to help resolve my problem.

I have been around a long time and understand exactly what is involved over time. The way money is paid means that individual minutes will rarely add to our cost, but people see the lacking minutes and get very emotional even when it does not affect their pay. The story is just too long to type out and post just so people will stop telling my why we should not do this rather than actually giving a little help.

I do not mean to be ungrateful, but the context is not what everyone in cyberland assumes it to be.

If you really do know how to add a minute to the calculation in the above code, I would be very grateful for any assistance you can offer. I have tried many different syntax and cannot find one that works. And everything I find on the net talks about adding and subtracting existing time entries, but not about how to add an absolute value. And the same things than seem to work in a formula in a cell do NOT necessarily work in code in a macro.

 

[Chuck D]

Adding 1 minute in excel time format would be would be +0:01:00 or can be abreviated to 0:01:

the entry must be in Hours, Minutes and secs as per Microsofts format
the first number entered would be Hours ie 0
the : indicates that you are using the time format and the next section will be minutes..Both digits must be entered ie 01
the next : indicated yo are moving on to seconds.
If seconds are 0 they need not be entered but the second : must be there

1 minute= 0 (Hours) : 0 1 (Minutes) : no seconds

No we are not all cyberbrains, this works for Excel, I am no genius and have yet no grip on VB..

Hope it is of some use... Chuck D.