Formula help please
 

I am trying to put together an auto-calulation for a timesheet.

The data will be entered as a 4 digit number eg: 0830 for 08:30am 1700 for
5:00pm etc...

what I have so far is:

a1) 0830
b1) 1700

c1) =(b1-a1)/100 in this case the answer is 8.7

but it is 8.5 hours

using 0800 and 1730 i get 9.3

should be 9.5 hours

How can I make excel give me these figures? is there any way of taking only
the digits right of the decimal place, then using a formula something like
d1) = c1(right of decimal)
e1) =if(d1=3,5,if(d1=7,5,if(d1=85,75,0)))
f1) =c1(left of decimal) (decimal) (e1) ie: 9.5
f20) =sum(f1:f19) for total hours.



TIA

Al

Formula help please
 

One way:

=MOD(TEXT(B1,"00\:00")-TEXT(A1,"00\:00"),1)*24


In article ,
estarriol33 wrote:

I am trying to put together an auto-calulation for a timesheet.

The data will be entered as a 4 digit number eg: 0830 for 08:30am 1700 for
5:00pm etc...

what I have so far is:

a1) 0830
b1) 1700

c1) =(b1-a1)/100 in this case the answer is 8.7

but it is 8.5 hours

using 0800 and 1730 i get 9.3

should be 9.5 hours

How can I make excel give me these figures? is there any way of taking only
the digits right of the decimal place, then using a formula something like
d1) = c1(right of decimal)
e1) =if(d1=3,5,if(d1=7,5,if(d1=85,75,0)))
f1) =c1(left of decimal) (decimal) (e1) ie: 9.5
f20) =sum(f1:f19) for total hours.



TIA

Al

Formula help please
 

Perfect!!!! Many thanks!!!!

"JE McGimpsey" wrote:

One way:

=MOD(TEXT(B1,"00\:00")-TEXT(A1,"00\:00"),1)*24


In article ,
estarriol33 wrote:

I am trying to put together an auto-calulation for a timesheet.

The data will be entered as a 4 digit number eg: 0830 for 08:30am 1700 for
5:00pm etc...

what I have so far is:

a1) 0830
b1) 1700

c1) =(b1-a1)/100 in this case the answer is 8.7

but it is 8.5 hours

using 0800 and 1730 i get 9.3

should be 9.5 hours

How can I make excel give me these figures? is there any way of taking only
the digits right of the decimal place, then using a formula something like
d1) = c1(right of decimal)
e1) =if(d1=3,5,if(d1=7,5,if(d1=85,75,0)))
f1) =c1(left of decimal) (decimal) (e1) ie: 9.5
f20) =sum(f1:f19) for total hours.



TIA

Al

Formula help please
 

Put the data in as 08:30 and 17:00
Then =B1-A1 will give time difference, or =(B1-A1)*24 will give you the
number of hours (formatted as number or general, not time).
--
David Biddulph

"estarriol33" wrote in message
...
I am trying to put together an auto-calulation for a timesheet.

The data will be entered as a 4 digit number eg: 0830 for 08:30am 1700 for
5:00pm etc...

what I have so far is:

a1) 0830
b1) 1700

c1) =(b1-a1)/100 in this case the answer is 8.7

but it is 8.5 hours

using 0800 and 1730 i get 9.3

should be 9.5 hours

How can I make excel give me these figures? is there any way of taking
only
the digits right of the decimal place, then using a formula something like
d1) = c1(right of decimal)
e1) =if(d1=3,5,if(d1=7,5,if(d1=85,75,0)))
f1) =c1(left of decimal) (decimal) (e1) ie: 9.5
f20) =sum(f1:f19) for total hours.



TIA

Al