calculating decimals

[Guest]

On our timesheets we must enter quarter hours as .15, .30, .45. Is there a
way to
enter these decimals but have excel calculate them as .25, .50, .75.
Thanks,

 

[Bernard Liengme]

Suppose A1 and A1 have you 'funny' times such as 2.15 and 3.45
The formula
=TIME(INT(A2),MOD(A2,1)*100,0)-TIME(INT(A1),MOD(A1,1)*100,0)+(A1>A2)
will return the difference (in hours and mins). The last term allows for A2
being the next day and numerically less than A1.
You will need to format the cell as [h]:mm
best wishes

 

[Arvi Laanemets]

Hi

Enter times as times. I.e. 8:15 or 16:45, etc. Then to calculate any time
interval you'll have ordinary substraction, like =B2-A2, and cell with
formula formatte as time too.

To use any time or time interval in calculations as number of hours, you
have to multiply it with 24 (24 hours equals to integer value 1). I.e when
you have p.e. entered into a2 a time value 3:15 , then 24*A2=3.25 (to see
the result this way, format the cell as general).

When you have to calculate time intervals which include midnight rollover,
use the formula like =B2-A2+(B2<A2)
(NB! This formula is valid only for time itervals less than 24 hours).

When you sum up times/time intervals in some cell, and the sum can exceede
24 hours, format the cell as "[h]:mm".


Arvi Laanemets

 

[Biff]

Try this:

=TIME(INT(A1),MOD(A1,1)*100,0)*24

A1 = 7.15

Formula returns: 7.25

Biff

 

[Guest]

For a comprehensive discussion about using Excel for timesheets, see
http://www.cpearson.com/excel/overtime.htm

 

[Guest]

Biff.. thanks it worked great. Now I need to add an entire column, of numbers.
A1:A45 Is there a way to do this without having to enter the formula you
gave me
for each cell in the total cell.
i.e. =TIME(INT(A1),MOD(A1,1)*100,0)*24 then I need to do the same thing
for cells A2 through A45. That would be a very long formula.
Thanks again for all your help.

 

[David Biddulph]

If you've got your formula in B1 referring to A1, & your source data in A1
to A45, you can select B1 and then select the bottom right-hand corner of
cell B1 (where you'll see a little black square) & drag down through B2 to
B45, or double-click that bottom right-hand corner of B1 and it will
auto-fill down to B45.

 

[Biff]

Try this:

=SUMPRODUCT(TIME(INT(A1:A45),MOD(A1:A45,1)*100,0)*24)

Biff