Calculate time differences with a twist

[Gilbertjoe]

I have a subform with the following fields
Time in - format as short time (12:00 AM)
Time out - format as short time (12:00 AM)
Hours - format as a number (2 decimal places)

I need to calculate the difference between Time in and Time out and then populate the Hours field after the user updates the time out field.

The twist is that I need the Hours field to be a number and to calculate the time in tenths of an hour (example: Time in is 1:00 PM and TIme out is 2:30 PM so hours would be 1.5)

 

[jdhilljr]

=SUM(B15-A15)*24

format cell as number

Jim

 

[Gilbertjoe]

Works but now every cell in the hours column till the very end of the sheet defaults to 0.0 if nothing is in the time in or time out cells. How do i prevent this?

 

[jdhilljr]

You can use a conditional format. Select all the cells then
Select 'format' then 'conditional format'
if cell value 'is equal to' 0 then click the format button and select white font this will hide all 0's

Jim

 

[neronikita]

I don't know if this will be helpful or not, but Excel views blank cells as 0 in calculations. Look at the Microsoft Knowledge Base Article 214244 (XL: How to create a formula to correctly evaluate blank cells) for more info on using the =IF(ISBLANK() formula.

Di

 

[Gilbertjoe]

Got it!
I selected tools then options then views and under the general tab i unchecked show zero values. That did what I needed without formulas.