Excel sheet time calculation on SharePoint

[Ella2314]

Hi, 

I have an excel file that shared on teams. I have a simple calculation formula 

e.g. =A2-B2 this cells contain 24h time list so I can calculate the working hours. 

the problem is with the evening shift it's starts at 16:00 and ends at 1:00 AM. when I try to update the end time to 1:15 the calculated cell don't show the number just ######## if the end time updated with in the same day e.g.  23:45 everything works but when it's the next day after midnight it shows ##### . when I open the file on the desktop app everything   works ok just on the SharePoint it don't work. is there any think I can do to fix it? 

[#2]

@Ella2314 

Don't think it has anything to do with Sharepoint, Teams or even Excel.
The problem is the formula:
It doesn't really matter where you enter this

  A1                B1          C1 = Formula B1-A1

16:00

01:00

##########

It's also logical that it shows that way...because it calculates incorrectly.

it's all about 24 hours, .and since it's more than 24 hours here...

    A1                     B1             C1 = Formula =IF(B1<A1,((B1+1)-A1)*24,(B1-A1)*24)

16:00

01:00

9

After that it should display correctly everywhere :)

Hope I could help you with these information.

NikolinoDE

I know I don't know anything (Socrates)

[#3]

@Ella2314 Alternatively you may use:

=MOD(B1-A1,1)