When calculating a time difference across midnight the result is negative
Observation: When entering in cell A1 the actual time stamp with ctrl+shift+; doing the same when the time wents at least 1 time across midnight and enter the then actual time stamp again with ctrl;shift+; into cell B!, the content of cell C1 with the formula =B1-C1 is wrong by -24h for 1 midnight crossing. As this was correct when I used 4.2 versions I compared 4.2.6 portable with 4.3.5.2 normal installation and found the the time stamp in both versions is different. 4,2 version time stamp: mm/dd/yy hh:mm:ss thus carrying the day information as well 4.3 time stamp: hh:mm:ss only no day information included. This causes the calculation error Expectation: Correct the time stamp ctrl+shift*: that day information is included again.
Correction of comment 2: Formula in C1 should be =B1-A1
That's consistent with Excel behavior. Previous one was a bug.
thanks for checking Urmas. You mean that an inserted time stamp is without day info? (Of course there is a good possibility to count with times by using appropriate cell formatting etc.)
I tried different format settings but the calculation across midnight is always wrong in 4.3. It is indeed consistent with Excel where ctr+: inserts the actual time without a date information. In Excel I cannot calculate a time difference across midnight as well. At least I did not find a solution. As I cannot calculate across midnight I regard Excel as faulty. How can we enter times in Calc (beside manually enter the information yy/mm/dd hh:mm:ss) and calculate across midnight? Can we have a function to enter the "current date & current time" and allocate a shortcut key to it (eg. ctr+:)? Then we would be consistent with (poor Excel) and have an additional feature compared to Excel. I re-opend the bug because I would like to get an answer to my question.
(In reply to bugquestcontri from comment #6) > I tried different format settings but the calculation across midnight is > always wrong in 4.3. > > It is indeed consistent with Excel where ctr+: inserts the actual time > without a date information. In Excel I cannot calculate a time difference > across midnight as well. At least I did not find a solution. As I cannot > calculate across midnight I regard Excel as faulty. > > How can we enter times in Calc (beside manually enter the information > yy/mm/dd hh:mm:ss) and calculate across midnight? > > Can we have a function to enter the "current date & current time" and > allocate a shortcut key to it (eg. ctr+:)? Then we would be consistent with > (poor Excel) and have an additional feature compared to Excel. > > I re-opend the bug because I would like to get an answer to my question. Hello. You can press CTRL+; (this insert DATE) and then on the same cell press CTRL+SHIFT+; (this insert TIME) -> result is date with time. Example: CTRL+; (14.01.2015) & CTRL+SHIFT+; -> result is 14.01.15 13:01 I'll close this as notabug. Maybe this can be enhancement "create shortcut for insert date&time".
Created attachment 112220 [details] sample counting with time see the sample for how it works for me
@ raal and @Cor Nouws - Thanks for the solution and the sample. This works on my XP machine. Hope I can make this to small macro. Should I have a bit time I will place an enhancement request for an additional combined entry of current date & current time. BTW, just as side note I might most likely file a bug report for a wrong function in v 4.3.5.2 on a Linux kubuntu 14.04 LTS machine. Need to check details again. If I can confirm what I saw I the bug report will be a separate one.