Add a minute to a time caluclation

[rjmccafferty1]

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?

 

[CaptainD]

That is because each entry is missing 59 seconds

8:00am - 9:15am = 08:00:00 to 09:15:00
skipping 09:15:01 to 09:15:59 (Not counting 100's)
9:16am - 10:15am = 09:16:00 to 10:15:00
skipping 10:15:01 to 10:15:59 (Not counting 100's)
10:16am - 11:00am = 10:16:00 to 11:00:00

 

[TonyJollans]

You should (or your users should) be consistent. If they enter three hours as 8:00 - 11:00, they should enter one hour as 9:15 - 10:15. You shouldn't have to change your calculations to cater for that kind of inconsistency, and if you must, then what are your criteria? When do you want to add a minute and when don't you?

If it helps, the way to do it is to add "0:01".

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[SkipVought]

Hi,

Ever here of GIGO?

Garbage IN, Gabage OUT.

If your people enter garbage, then that's what you have.

Explain it to them this way. Let's say the workday is 8 AM to 4 PM, 12 hours. Let's say they record periods that they work.

8:00 to 12:00 -- FOUR hours.
1:00 to 4:00 -- THREE hours.

Where's the missing hour? It's pretty obvious, is it not?

So why are they doing THAT SAME THING with minutes???!!!

Do it RIGHT and forget adding missing minutes!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[macropod]

Hi rjmccafferty1,

Looked at another way, if the person started at 9:16am and finished at 10:15am, they didn't work for an hour. The number of minutes elapsed between 9:16am and 10:15am is 59, not 60. So pay them for the 59 they say they worked for. They'll soon catch on.


Cheers
[MS MVP - Word]

 

[rjmccafferty1]

I understand everyone's point about telling other people they need to do things the right way.

I stopped tilting at the windmill of perfection long ago, it just is a lot easier and more peaceful to do what people ask. In this case, I am asked to just add a minute to the times in the macro rather than trying to force a lot of people to conform to a rule that they do not understand and do not want to understand. In the long run it is cheaper and easier. The owner's understand they may end up paying for a few minutes of extra, unearned time and feel it is worth it to avoid what would be involved in demanding "just so" entries from independent contractors and do keep doing it with everyone new that comes on board along the way.

If anyone knows how to add 1 minute in VBA, I would really appreciate hearing. Just adding "00:01" in the above VBA code does NOT work. If you doubt me, put it into an Excel macro and give it a try.

If you do not know how to do it, that if fine. But it is a waste of your time and mine to rail at people who do not do things "right".

 

[macropod]

Hi rjmccafferty1,

And just how many miscreant contractors are there? After 36 years in the workforce, many of those working alongside contractors, this is the first time I can recall anyone ever having an issue with correctly recording their start and finish times. If the contract says the contractor is to record start 7 finish times with a 1-minute accuracy, then 9:16am-10:15am should be interpreted as meaning 59 minutes.

In the long run it is cheaper and easier.

Easier maybe, but definately not cheaper. In fact, adding a minute to the time worked by everyone who understands timekeeping 101 is going to cost far more in the long run that doing it right. As for the others, it's as I said: They'll soon catch on. If a contractor can't figure out something so basic, do you really want them working for you?

Question: Is the person telling you to do this going to pay the extra out of their own pocket? Somehow I doubt it and, when the bean-counters notice the overpayments, who do you think will get the blame?


Cheers
[MS MVP - Word]

 

[TonyJollans]

macropod is absolutely correct - do you really want to employ contractors who can't record their time properly?

If, however, you insist on knowing how to do it, you must answer the question I first asked. What are the criteria? When do you want to do it? 8:00 - 9:15 is one and a quarter hours, 9:16 - 10:15 is one hour. Always? Or just when the previous end time is 9:15? Is it always one minute? Or might it be two? Do you always want to add, or sometimes subtract? Should you round up? Or down? To the nearest hour? Or quarter hour? Or what?

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[strongm]

Here's a minor modification of your macro code to meet your original requirements. But, as Tony points out, there are a lot of assumptions built in to this:

[blue]Dim lLastGRow As Long
    lLastGRow = ActiveSheet.Cells(Rows.Count, "G").End(xlUp).Row
    Range("H2:H" & lLastGRow).FormulaR1C1 = "=(RC[-1]-RC[-2])*24"
    Range("H2:H" & lLastGRow - 1).FormulaR1C1 = "=(RC[-1]-RC[-2]+TIMEVALUE(""00:01""))"
    Range("H" & lLastGRow).FormulaR1C1 = "=(RC[-1]-RC[-2])"[/blue]

 

[SkipVought]

faq68-5827

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[strongm]

Hello?

 

[fumei]

ta-ta?

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[SkipVought]

Usta be, there was Konrad Adenauer & Red Auerbach.

In the Fall you Adenauer.

In the Spring you get an Auerbach.

There is also the ADA Dental Minute (its a stretch), so maybe that's where we are going.

[sub]Is it Friday afternoon yet?[/sub]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[fumei]

punny man.

Yes, it sure the heck IS Friday afternoon.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry