0

On Google Sheets, I am trying to create a formula within a single cell that calculates the positive or negative number of days between two dates. Specifically I am trying to determine how much buffer in days between two dates, positive or negative. I want it to automatically change to positive or negative correctly to determine how many days we have between the two dates OR how many days we don’t have (negative days). Having a hard time explaining lol.

For example:

Column A: 1/1/2022

Column B: 1/2/22

Column C (Days): 1

————

Column A: 12/1/2022

Column B: 11/2/22

Column C (Days: -30

For whatever reason I can wrap my head around this and keep getting errors when using if(date 1>date 2, datedif(date 1, date 2, “d”), -datedif(date1, date 2, “d)) for example which I thought would have thought worked :(.

Any ideas? I feel like there’s a very simple solution but I can’t quite crack it (coding is not my strong suit).

1
  • Welcome! Why not simple =B2-A2? By the way, between 12/1/2022 and 11/2/22 29 days, not 30
    – JohnSUN
    Sep 22 at 6:01

1 Answer 1

0

The results you described in your question can be obtained with a formula like this:

=B2-A2-(B2<A2)

Sample

3
  • Yes! This is exactly what I was looking for! Thank you so much! Seriously—lifesaver! I did notice one odd bit about this: it seems to add an extra -1 to any negative number. For example: 1/19/22 & 1/18/22= -2 instead of -1. Any ideas? Sep 22 at 17:01
  • @HaydenRobel Well, yes, I adjusted the negative result to the sample from your question: -30 days. If this amendment is not needed, then discard part -(B2<A2) of the formula, leave only the =B2-A2 as in the first comment.
    – JohnSUN
    Sep 22 at 17:13
  • Ah that makes sense! Thank you again so much! It really was right in front of me this whole time but couldn’t wrap my head around it still 😫. Thank you again! Sep 22 at 17:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.