Okay so I'm thinking I'm not thinking about this properly. I have a spreadsheet with two tabs. On Sheet2 I want a formula that figures out how many days are left (cell A4), subtracting the closest date on Sheet1 (column A) from today. So today is 4/10/2023. The closest date on Sheet1 is 4/17/2023. That would be 7 days. Now, if it were 4/18/2023, then the closest would be 5/1/2023, but the days would be 21.
Any ideas? I feel like I'm missing something simple.
TryFormula:
Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
why 21
18/4 to 1/5 is 13 days
anyway try
=INDEX(Sheet1!A2:A36,1+MATCH(Sheet2!A2,Sheet1!A2:A36,1))-A2
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or, if you have LET, then a bit neater,Formula:Please Login or Register to view this content.
Okay so I'm going with TMS's second formula - I had no awareness of LET formulas and I appreciate learning totally new stuff.
etaf - maybe the difference is in formatting?
I'm referring to the number of days between April 10th and May 1st, which is 21.
OK, but you did say if the day was the 18th not the 10th
confused
TMS has the same result - 13
Lol, my mistake - I'm so sorry, I was extrapolating and then didn't follow through. It didn't even occur to me when I responded to you that I'd changed it to the 18th.
All three formulas work.
you are welcome
You're welcome. Thanks for the rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks