Convert time caluation to hour format not decimal.

[tbobolz]

Excel 2010

Hi, I have the below formula which returns the correct answer but in decimal format. It then does not allow the column to be formatted in a time value.

So the formula returns the value example of 1.25 (1 and a quarter hours), when I want it to return 1:15 (1 Hour and 15 minutes).

=if(or(Table1[ER ARRIVAL DATETIME]="",Table1[ER DEPARTURE DATETIME]=""),0,24 * (Table1[ER DEPARTURE DATETIME]-Table1[ER ARRIVAL DATETIME]))


Any thoughts on correcting this.

Thanks
Terry

 

[Scott Huish]

Take off the 24 *

 

[tbobolz]

Thanks for the help Scott. I tried to remove te 24 *, but returns a vaule of .272222. still does not allow me to update the Data Type to date format. It should return about 6:32. With the 24 * it returns 6.5333.

Here's what I am subtracting.
2/3/2014 3:28:00 PM -
2/3/2014 10:00:00 PM

Again, I appreciate the help!

 

[tbobolz]

Ok, I got it. I had spent so much time on it I forgot to reformat my pivot after I removed the 24 *.


Thanks!!!!!!!!!!!!!!!!!! Scott.