I want to note a day change [closed]

asked 2013-10-01 15:43:56 +0200

Fred gravatar image

I have a spreadsheet to log battery life between charges. I want to note a day change and subtract 8 hrs. (sleep time) if the day has changed. Currently I am using IF(DAY(B2)-DAY(A2)>0,-8). This works fine except for the first day of the month. See below

Day1              Day2              Diff            Diff        If
date time       date time           DAY(B2)-DAY(A2) DAYS(B3,A3) IF(DAY(B2)-DAY(A2)>0,-8)
29/09/13 18:20  30/09/13 08:20      1               0.5833333333    -8.00    Correct
30/09/13 18:20  01/09/13 08:20     -29.00          -29.4166666667   0.00     Wrong

Any Ideas?

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-10-05 00:26:40.384528


May be =INT(B2)-INT(A2)? But please keep in mind that the function IF() without the third parameter (ELSE) can lead to incorrect results.

JohnSUN gravatar imageJohnSUN ( 2013-10-01 16:27:51 +0200 )edit