CODEWITHSUNDEEP
exceldate
Philalethes
Philalethes

Reputation: 115

Best Way to Subtract User Inputted Date in Cell From a Set Date (Day/Month) Irregardless of the Year

I am trying to establish the best way to write a formula to take a date that is inputted in a cell by a user and subtract it from the last day of the month (the due date), not taking into account what year it is. The cell that contains the formula will be in another cell, pointing at the user inputted date cell.

I have 12 columns running across with each with one of the months of the calendar year. In each column for the corresponding month is where the user will input the date they completed a task. I would like to subtract their input from the last day of the corresponding month in that column. I want to reuse this spreadsheet every year, so I do not want to account for the year in my formula. I hope this will prevent me having to update the formulas each year.

An example would be if the user inputted 2/24/17 into C7, because the column C is the column for February, I would like the result to be -3 (I am not worried about leap years at this point). If they inputted 3/11/17 into the same cell (C7), they would get a result of 11, because the date is 11 days past the due date.

Here is the formula that I started to use, but I believe it is relative to the current year.

=IF(ISBLANK(C7),"",C7-(DATE(YEAR(TODAY()),MONTH(1)+2,1)-1))

Here is a link to a picture of my spreadsheet. Thank you in advance for your help!

Calendar Spreadsheet

Solution Changed YEAR(TODAY()) to YEAR(target cell). My new formula looks like:

=IF(ISBLANK(C7),"",C7-(DATE(YEAR(C7),MONTH(1)+2,1)-1))

Upvotes: 0

Views: 51

Answers (1)

pnuts
pnuts

Reputation: 59485

Maybe:

=IF(C7="","",C7-EOMONTH(C7,-1))

(based on @barry houdini's Comment and subsequent OP clarification via a Comment).

Upvotes: 0

Related Questions