Excell spreadsheet formula

[Guest]

I have a worker who gets $10 daily for a job, but he's got to be there
promtly @7:30am. For each 5 minutes late, $1.00 is docked.

I'd like to be able to enter into the cell 7:33am and this will bring up
$9.00. 7:37am 8.00 and so on.

Any help for setting this up would really be appreciated.

Thanks all

 

[Guest]

Hi,

First, you are underpaying your people.

Here is a formula that does that:

Assume that the 7:30 is entered in B1 and 7:33 is in C1 then:

=10-IF(C1>B1,ROUNDUP((C1-B1)*24*60/5,0),)

 

[Gord Dibben]

Secondly.........if worker is 41 minutes late gets paid nothing for the day.

Not much incentive to show up in that case.

Hope you have an abundance of workers waiting in the wings.

But maybe in the third-world country where you pay $10.00 a day there might be
many starving people willing to be called.

I'm just hoping this was example only.


Gord Dibben MS Excel MVP

 

[Guest]

Hi, guys. Very nice of you in replying so fast and trying to help me.

1. Perhaps I didn't explain enough. It's daily but only for like 45 min. or
so. The job is basically sitting doing NOTHING. But I need it for something.
2. And yes, if he's 41 minutes late..........well he/she might stay
home.......lol.

3. This is what I entered in a seperate cell and it returns 0:00.
=10-IF(D12>C12,ROUNDUP((D12-C12)*24*60/5,0),)

d12= 7:30am
c12 = 7:33am

What did I do wrong?

Thanks again all.

 

[Guest]

Hi,

Excel picks up the time format from the referenced cells, just format it
back to a number.

 

[Gord Dibben]

Larry

Did you enter the times in C12 and D12 as times?

If you entered then as 7:30am Excel sees that as text, not a time.

C12 7:30:00

D12 7:33:00

Shane's formula in E12 returns 9

Thanks also for clearing up about the job duration.

Now we can back off with the critiques about you being a cheapskate<g>


Gord

 

[David Biddulph]

7:30AM is text, but 7:30 AM (with a space between) can be a time.