Excel formula

New Contributor


A question for calculation hours not coming in decimal, for example start hours 21:00 - 06:00 my calculation will come to 09:00 I need to have just 9 not decimal as it affects on all of my formula in total calculation.   

5 Replies

@Neda1978 Not sure how you arrive at 9 hours by deducting 6:00 from 21:00, but never mind. A time (or duration) of 9:00 is stored in Excel as the number 0.375, being 9 / 24. So, if you want to display 9:00 as 9 you need to multiply it by 24.


@Riny_van_Eekelen Thanks for your reply. I used this formula, =IF(AFD96>AFE96,AFE96+1,AFE96)-AFD96 how I can multiply it by 24? Sorry I am very bad in formula. 



Your formula would be:

=24 * (IF(AFD96>AFE96,AFE96+1,AFE96)-AFD96)


But, you could also use:

=24 * MOD(B2-A2, 1)




Unfortunately not working it comes to 00:00. The hours is 21:00 - 07:00 in roster not based on Am and PM.

Sorry The hours is 21:00 till 06:00 not based on am or pm. I tried so many times with 24*, it comes to 00:00. Also Just wanted to read as 9 without decimal.



If your cell is showing 00:00, then you need to change the format from a time format (hh:mm) to a number format. In Excel, the integer part of a number is days and the decimal is time. An hh:mm format will only display the decimal part, which is zero, so you need to change the format to see the integer.