Forum Discussion
Help with date math please
I'm using this formula to calculate the number of days between 2 dates and return blank if the one date hasn't been filled in yet:
=IF(AS32<>"",AS32-AS39,"")
Where:
AS32 is a manually-entered date formatted in mm/dd/yyyy format and
AS39 is a date in mm/dd/yyyy format that is linked to another date that is in mmm-yy format then formatted custom mm/dd/yyyy with the dd swapped out for "20" as that's the date for day 0 in the formula.
The error I'm seeing (in my process) is that excel is adding varying numbers of days based on 30 or 31 day months. So if I subtract 3/20/2027 from 3/21/2027, I'm getting 5. Subtracting 1/20/26 from 1/21/26 is returning 15. Both should return 1.
I'm guessing that changing the dd to "20" doesn't make the displayed date actually what is being displayed. If this is accurate, is there a better way to change date format from mo/yr to mo/(the 20th)/yr?
I've attached a modified copy of the sheet with the problem areas highlighted in yellow.
What you believe to be March 20 is in fact March 16, custom formatted to be displayed as March 20. What did you have in mind with that custom format in AS39?
What you believe to be March 20 is in fact March 16, custom formatted to be displayed as March 20. What did you have in mind with that custom format in AS39?
Thank you for looking at this and replying. So the top date in row 2 is (as you can see) just the month and year. Actually I think you pointed me in the right direction. I changed the dates to 3/20/2026 and was able to drag to the right to increment. This also fixed the days math!!!
Thanks 🙂
Replace the date format for AS39 with mm/dd/yyyy and change the formula to:
=DATE(YEAR(AS2),MONTH(AS2),20)This is a great solution as well! Thank you!!
