Capturing Upcoming Dates

ely6678

New Member
Joined
Jan 28, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello,

I'm working on the attached sheet and want to be able to capture upcoming due dates based on today's date. I've tried the MIN function and can't seem to get it to work as the date returned is in the past (M5), rather than >= in the future. I would also like to be capture due dates sequentially, so (M5) would capture the first upcoming date, followed by next in (M6), etc., through (M15).
 

Attachments

  • ContractList Snap.jpg
    ContractList Snap.jpg
    191.4 KB · Views: 10

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Hi,

Welcome to MrExcel forum.

The screenshot is not showing the column name and row numbers so have to assume or count the cells. Please share the complete screenshot showing column and row number.

In Option Date you are comparing column B data with Date. But column B doesn't contain Dates.

Also, great if you share the expected output.

Thanks,
Saurabh
 
Upvote 0
Thank you for the reply. I've added an additional screenshot with column/rows showing. The output that I'm trying to get is to have column M show the next upcoming option date in order, so M5 should reflect Jan 30, M6 Feb 1, etc.

I'm currently using =MIN(IF((B5:B34>=TODAY()),G5:G34)) or (IF((range>=TODAY()),range)). When I attempted to use the date column (column G) as my range in both instances but receive 1/0/1900 as the output, putting B5:B34 as the first range was the only way to get logical output, however even there the output is incorrect. That said, I may be walking down the wrong path completely in solving this formula.

Thank you again for the response and any assistance you can offer.
 
Upvote 0
Thanks for sharing.

MrExcel has an excellent tool “XL2BB”. It helps you to share Excel data, formula in the post and we can Copy/ paste the same to our Excel spreadsheets.

Please check this link for the tool : XL2BB Add-in

You can test the above using this link: "Test Here
 
Upvote 0
Welcome to the MrExcel board!

=MIN(IF((B5:B34>=TODAY()),G5:G34))
This seems to have nothing to do with your data as column B appears to be text values. Never-the-less see if you can use something like this.
If still having trouble, check that your date cells are actual dates (numbers) not text values. eg See what =ISNUMBER(G6) returns.

21 01 29.xlsm
DEFGHI
1
229/01/2021
3
4
52/05/20217/02/2021
68/04/202119/02/2021
727/03/20213/03/2021
819/02/202115/03/2021
915/11/202027/03/2021
102/01/20218/04/2021
1121/12/202020/04/2021
1214/01/20212/05/2021
1326/05/202114/05/2021
1427/11/202026/05/2021
153/03/2021
167/02/2021
1720/04/2021
1815/03/2021
1926/01/2021
209/12/2020
2114/05/2021
22
Ascending Future Dates
Cell Formulas
RangeFormula
D2D2=TODAY()
I5:I14I5=SORT(FILTER(G5:G21,G5:G21>=D2,""))
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top