Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|