Forum Discussion

Neda1978

Copper Contributor

Sep 29, 2022

Excel formula

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 formu...

excel

Riny_van_Eekelen

Platinum Contributor

Sep 29, 2022

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.

Neda1978
Copper Contributor
Sep 29, 2022
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.
- JMB17
  Bronze Contributor
  Sep 29, 2022
  Neda1978
  
  Your formula would be:
  =24 * (IF(AFD96>AFE96,AFE96+1,AFE96)-AFD96)
  
  But, you could also use:
  =24 * MOD(B2-A2, 1)
  - Neda1978
    Copper Contributor
    Sep 29, 2022
    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.