Calculate time as a whole number

[yoelpa]

I have a time sheet in col. A is time in in col. B is time out. I want to calculate the time but the total number should be a dec. number not a time value.
Also I wan it should round to the nearst 15 minutes
Any Ideas?

 

[Brian from Maui]

I have a time sheet in col. A is time in in col. B is time out. I want to calculate the time but the total number should be a dec. number not a time value.
Also I wan it should round to the nearst 15 minutes
Any Ideas?

See:

http://www.mrexcel.com/board2/viewtopic.php?t=36153&postdays=0&postorder=asc&start=0

 

[IML]

You could use
ROUND((B1-A1)*24/0.25,0)*0.25

format cell as number

 

[IML]

Per your private message, the logic can be broken into two parts:

Time:
Excel stores time as fraction of a day, where 1 = 24 hours. So by subtracting time, excel storing this as a fraction. for example subtracting 18:00 and 6:00 with has excel storing .5 as a value if you change format to general. by multiplying this by 24, it will reflect the inter 12. Change 18 to 18:30 this changes what excels stores to approximately .52, which shows as 12.5 hours when mutliplied by 24.

The time portion of the formula is all in this part of the formula
(B1-A1)*24

Rounding:
This works for all numbers. Lets just say you wanted to round a number, 7, to the nearest 5. The forlmula would be
=ROUND(7/5,0)*5

excel just caluates 7/5 which is 1.4, rounds it the nearest whold number (1) and that multiplies it back by 5 - for a result of 5.

Change the number to 7.5 and the divsions result is 1.5, which rounds to 2 for a result of 10.

 

[Mark W.]

=(CEILING(B1-A1,1/96)*24)

 

[IML]

or rather
=(MROUND(B1-A1,1/96)*24)

if I understand the requirements correctly (analyst tool pak).

I like the 96 (96 15 minute periods in a day )