Forum Discussion

Ella2314's avatar
Ella2314
Copper Contributor
Sep 07, 2022

Excel sheet time calculation on SharePoint

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? 

excel
Excel for web

2 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Sep 08, 2022

    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:0001: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:0001:009

     

    After that it should display correctly everywhere 🙂

     

    Hope I could help you with these information.

     

    NikolinoDE

    I know I don't know anything (Socrates)

Resources