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

 

[Aladin Akyurek]

On 2002-02-17 03:56, Jakeyboy wrote:
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

The formula that you can use is:

=(B1+(B1<A1)-A1)*24

where A1 houses the start time and B1 the end time.

Format the cell of this formula as General.

 

[Jakeyboy]

Many thanks Aladin, but this formula doesn't work I'm afraid.

 

[Aladin Akyurek]

On 2002-02-17 04:24, Aladin Akyurek wrote:

On 2002-02-17 03:56, Jakeyboy wrote:
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

The formula that you can use is:

=(B1+(B1 < A1)-A1)*24

where A1 houses the start time and B1 the end time.

Format the cell of this formula as General.

 

[Aladin Akyurek]

On 2002-02-17 06:49, Jakeyboy wrote:
Many thanks Aladin, but this formula doesn't work I'm afraid.

Jake: The formula was crippled, because of the existence of the less than sign. I thought the new board would not have this symptom of the old board. It appears we have still to watch out for it. See the one that is fixed.

 

[Jakeyboy]

Aladin,

Many thanks, the repaired version has worked perfectly. I know who to ask if I've any more problems

Thanks again

 

[keithkemble]

This reply in in sync with ALadin AKyurek question on Overtime

Further to my previous answer you can change column f to this and this will return zero if there is no start and finish times.

If you change the cell F1 to
=IF(AND(A1=0,B1=0,TRUE),0,IF(C!=0,24,D1*24))
This will test for nil entry in both start and finish times

This is because in our business we work 365 days and you could have the same start and finish time which would mean a day has expired
Hope this helps
kk

 

[Guest]

HOw bout this problem.
1 hour = $25
30 minutes = $15
less than 30mins = $10

Time In TimeOut Total Time Charge
6:30 pm 7:00 pm 30mins $15
7:30 pm 9:00 pm 1hour30mins $40

how do i create the formula from Total Time and Charge... Is it possible that the formula automatically calculates the charge time taking in consideration that for a half hour its just 15$? and that usage for less than 30minutes would be $10?

I really appreciate the help here... thanks

 

[empoy]

The anonymous post was my post... need the help a lot.. thanks

 

[Aladin Akyurek]

On 2002-02-18 07:31, Anonymous wrote:
HOw bout this problem.
1 hour = $25
30 minutes = $15
less than 30mins = $10

Time In TimeOut Total Time Charge
6:30 pm 7:00 pm 30mins $15
7:30 pm 9:00 pm 1hour30mins $40

how do i create the formula from Total Time and Charge... Is it possible that the formula automatically calculates the charge time taking in consideration that for a half hour its just 15$? and that usage for less than 30minutes would be $10?

I really appreciate the help here... thanks

Given a total time in the format 2:45, 7:40, etc., it seems you're looking for:

=HOUR(E1)*25+(MINUTE(E1)>=30)*15+(MINUTE(E1)>30)*10+(MINUTE(E1)<30)*10

where E1 holds a total time.

Perhaps I misunderstood, but the payment scheme looks a bit irrational to me.