rjmccafferty1
MIS
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?
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?