Formatting time/decimal

[Jakeyboy]

I have a similar problem. I am a night shift worker, and my employer issues a time statement each week in the format of start time, end time, total. The problem is that in order to reproduce this in Excel, I must calculate accordingly. My result looks like this: 18:45, 08:30, 13:45. My time statement reads thus: 18:45, 08:30, 13.75. For the life of me, I can't get the calculation, (formula?) to same result. Help would be appreciated.

Thanks in advance

Jake

 

[empoy]

I based my formula on what you gave me but i still get a wrong answer.

anyway the payment scheme is like these
A B C D
1 6:30 8:00 1:30 40
2 6:32 7:10 0:38 18.36

my formula i did was this
=HOUR(C2)*25+IF((MINUTE(C2)>=30),(MINUTE(C2)>=30)*15+(60-MINUTE(C2))*0.42,(MINUTE(C2)<=30)*15+(MINUTE(C2)-30)*0.42)

its a bit confusing for me but this is how i understand how our payschem should be
1 hour = 25$
30 mins = 15$
execess minutes like would be charged at 0.42c

meaning if i worked for 38minutes my payment would be 15+3.36= 18.36 or if i worked 2 hours and 35minutes i would get
50+15+2.1=67.1$



but with the formula i did i get 24.24.. anybody could help me correct my formula?

thank you very much

 

[Aladin Akyurek]

On 2002-02-18 20:49, empoy wrote:
I based my formula on what you gave me but i still get a wrong answer.

anyway the payment scheme is like these
A B C D
1 6:30 8:00 1:30 40
2 6:32 7:10 0:38 18.36

my formula i did was this
=HOUR(C2)*25+IF((MINUTE(C2)>=30),(MINUTE(C2)>=30)*15+(60-MINUTE(C2))*0.42,(MINUTE(C2)<=30)*15+(MINUTE(C2)-30)*0.42)

its a bit confusing for me but this is how i understand how our payschem should be
1 hour = 25$
30 mins = 15$
execess minutes like would be charged at 0.42c

meaning if i worked for 38minutes my payment would be 15+3.36= 18.36 or if i worked 2 hours and 35minutes i would get
50+15+2.1=67.1$



but with the formula i did i get 24.24.. anybody could help me correct my formula?

thank you very much

In D2 enter: =HOUR(C2)*25+(MINUTE(C2)>=30)*15+(MINUTE(C2)>30)*(MINUTE(C2)-30)*0.42

and copy down.

Not only the specs are changed, but also your username, I think. Right?

Aladin

 

[empoy]

Thanks very much aladin i eventually got it before you replied to my message but your formula is much simpler mine's this

=HOUR(D8)*25+IF(MINUTE(D8)<30,MINUTE(D8)*0.42,((MINUTE(D8)-30)*0.42)+(MINUTE(D8)-(MINUTE(D8)-30))*0.5)

any comments on my formula? thank you again....