fpspreadshet calculates wrong when the time is 00:00 lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- Soner · 2018-08-30 11:42 fpspreadshet calculates wrong when the time is 00:00. open the attached file with Libreoffice and spready and look at cells E10..E17 (values) and H10..H17 (formulas). (I wanted write bug report but I could not remember my account from this computer). ---------------------------------------------------------------------- wp · 2018-08-30 11:58 Can you reproduce this with a simpler formula? My first idea is that this could be due to some round-off error since you calculate and compare differences between TDateTime values which are floating point numbers. ---------------------------------------------------------------------- Soner · 2018-08-30 12:21 It looks like parser error. Look the formula for h10: =(IF(E10="",,(IF(E10>D10,E10-D10,((1-D10)+E10))*24))- (IF(G10="",,(IF(G10>F10,G10-F10,((1-F10)+G10))*24)))) You can simplify it  when you remove second part after - and add ) : =(IF(E10="",,(IF(E10>D10,E10-D10,((1-D10)+E10))*24)) ) H10 shows now nothing.(It should show 13,00) One more step simpler: =IF(E10>D10,E10-D10,((1-D10)+E10))*24 now it shows 13,00 Edit: 13 is correct value for simplified formula for row 10. (calculating of worktime only "Arbeitszeit") ---------------------------------------------------------------------- Soner · 2018-08-30 12:25 Quote from: Soner on August 30, 2018, 02:21:07 pm.. =(IF(E10="",,(IF(E10>D10,E10-D10,((1-D10)+E10))*24)) ) H10 shows now nothing.(It should show 13,00) ... I found this error. It calculates 00:00 as nothing. Change this formula to: =(IF(E10="",999,(IF(E10>D10,E10-D10,((1-D10)+E10))*24)) ) then it will show 999. ---------------------------------------------------------------------- Soner · 2018-08-30 12:37 really fpspreadsheet calculates 00:00 as nothing when the cell is time field. Look the formula at C40. It shows 2, because only the cells E12 and E12 has different value then 00:00. ---------------------------------------------------------------------- Soner · 2018-08-30 12:51 @wp: Wrong calculation is caused only by 00:00. -7 is the pause part, because first part results 0 and 0-7 is -7. Change this: =(IF(E10="",,(IF(E10>..... to =(IF(E10="",10,(IF(E10>..... then you get 3. ---------------------------------------------------------------------- Soner · 2018-08-30 15:04 If found another error. When you compare string value with a time or number formatted cells then you get sometimes wrong value. Look the formulat at the cells N43 and N44 from attached file. ---------------------------------------------------------------------- Soner · 2018-08-30 15:21 If found again an error. When one sheet contains link to other sheet and you save it as ods- format the you can't open it with fpspreadsheet. But LibreOffice can open it. I have 2 files in attachment, i created them with my program as xlsx- format and as ods-format. Spready can open the file xlsx-format but it can not open ods-format. Now when you open ods-file in libreoffice and delete the liste-sheet then spready can open it, because liste-sheet contains formula with link to other sheet. ---------------------------------------------------------------------- wp · 2018-08-30 15:38 Quote from: Soner on August 30, 2018, 01:42:21 pmfpspreadshet calculates wrong when the time is 00:00. Fixed in ccr-r6627. The problem was that an empty cell was considered in a comparison operation to have the value 0. ---------------------------------------------------------------------- wp · 2018-08-30 17:49 Quote from: Soner on August 30, 2018, 05:21:26 pmWhen one sheet contains link to other sheet and you save it as ods-format the you can't open it with fpspreadsheet. But LibreOffice can open it. Fixed in ccr-c6629. Here the problem was that ODS quotes sheets reference in addition to the square backets that they always put around the sheetnames. In addition there is also a '$' in front of the quoted sheet names. Seems like overkill to me. But should work now. ---------------------------------------------------------------------- Soner · 2018-08-31 09:34 Thanks, it works, you are great! LibreOffice developer can learn something from you.