Calendar code to be used in a formula within a macro

[charles1394]

Good Evening,
I did some searching and couldn’t find anything that specifically addresses the problem I’m trying to solve. I’m trying to learn VBA on the fly, so sorry if this is a dumb question. That said, I have data that includes a ‘suspense date” for when the next action should occur. I have a formula that buckets the suspense’s into categories based upon “today”’s date. The only problem is that sometimes I need to go back and look at old data for comparison, but each time I run the formula, embedded in my macro, it references “today”, which skews the bucketing. I’d like to have users select, via a calendar, what the “today” date would have been given the date of the data, where the default is the day prior (our data is as of close of business the day before (not real time). Therefore, if the user hit’s ok, the default will be the day prior to “today”, unless the user selects something earlier. My current formula is below and in the attached workbook. I don’t have the VBA code in the workbook, however. Any help would be greatly appreciated. (NOTE: THe workbook only includes the limited data/cells in question so it does not align to the code below (i.e. formula in column B in the workbook but code is referencing column O)

ActiveCell.FormulaR1C1 = "CMS Suspense Category"
Columns("O:O").Select
Selection.NumberFormat = "General"
Range("O2").Select
ActiveCell.FormulaR1C1 = _"=IF(ISBLANK(RC[-1]),""REVIEW"",IF(RC[-1]-TODAY()<0,""Past Due"",LOOKUP(RC[-1]-TODAY(),{0,1,8},{""Due Today"",""Coming Due in 7 Days"",""Coming Due over 7 Days""})))"
 Selection.AutoFill Destination:=Range("O2:O" & LastRow)

ForumHelp.xlsx