Sep 07 2022 01:34 PM
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?
Sep 07 2022 11:44 PM
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.
I know I don't know anything (Socrates)
Sep 08 2022 12:03 AM - edited Sep 08 2022 12:04 AM