Excel formula

Question

Hi,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.&nbsp; &nbsp;

riny_van_eekelen · Answer

Neda1978&nbsp;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.&nbsp;

neda1978 · Answer

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

jmb17 · Answer

Neda1978&nbsp;&nbsp;Your formula would be:=24 * (IF(AFD96&gt;AFE96,AFE96+1,AFE96)-AFD96)&nbsp;But, you could also use:=24 * MOD(B2-A2, 1)&nbsp;&nbsp;

neda1978 · Answer

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.

jmb17 · Answer

Neda1978&nbsp;&nbsp;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.

Forum Discussion

Excel formula

5 Replies

Resources