Forum Discussion
Number of days + or -
I am trying to get the difference in the number of days between two dates and it works if the target completion date is before the completion date using this formula: =DATEDIF(H3,J3,"d"). I do get the correct number of days using this formula, however, I would ideally like is a plus or minus in front of that number (e.g., + if the project went past the target date, and - if the project was completed sooner). I can apply conditional formatting to the rows after to change the color of the number if I get that far.
When the completion date is before the target completion date (e.g., completed project early), I get the #NUM! error, which I expected. But I cannot find a way to get exactly what I am looking for. Hopefully I am explaining this clearly. The formula in L4: =DATEDIF(H4,J4,"d")
4 Replies
- William_SonCopper Contributor
The #NUM! comes from DATEDIF — it errors when the end date is earlier than the start date. If you just need the signed difference, plain subtraction does it: end minus start (for example =J4-H4) returns a negative number when the end is earlier, with no error. And if you want the plus sign to actually show on positive results, apply the custom number format +0;-0;0 to the cell — positives get a +, negatives keep their minus, and zero shows as 0. (Disclosure: drafted with AI assistance, checked by me.)
- SnowMan55Bronze ContributorWith Excel's default date settings (the "1900 date system"), Excel dates are internally represented as the number of days since (approximately) 1 Jan 1900. So you can simply subtract one date from another to get the difference (positive, negative, or zero).