Return value based on nearest date

D

drawlings

New Member
Joined
Aug 25, 2007
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Hello

I am using the below formula to return the next action date from a table and this works as expect :)

Excel Formula: 
=IFNA(INDEX(tbActions[Action Date],MATCH(MIN(IF(tbActions[Pillar]=C7,IF(tbActions[Status]<>"Completed", ABS(tbActions[Action Date]-TODAY())))),IF(tbActions[Pillar]=C7,IF(tbActions[Status]<>"Completed",ABS(tbActions[Action Date]-TODAY()))),0)),"None")

So I am stuck on the following, in the table I am looking up (tbActions) I have an Id column (Id) that I also need to return based on the next date. I am reaching out for suggestions so that the 'Action Item' Column now includes the Id

1686046186271.png


1686047454372.png


Thanks in advance

Dan
 
Last edited:

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
what version of excel are you using?
And is it possible to use the xl2bb add in to post your data (Link below)? If you can't do that can you post the data in a table (vs an image)? With images the forum needs to recreate your scenario from scratch. Errors in omission and assumption can lead to more work and worse - bad formulas that work. Please help the forum help you best.

Thanks in Advance.
 
Upvote 0
awoohaw said:
what version of excel are you using?
And is it possible to use the xl2bb add in to post your data (Link below)? If you can't do that can you post the data in a table (vs an image)? With images the forum needs to recreate your scenario from scratch. Errors in omission and assumption can lead to more work and worse - bad formulas that work. Please help the forum help you best.

Thanks in Advance.
Click to expand...
Thanks, I am using Excel 365. See the mini sheets below

CDW - Sample.xlsx
CDEFGHI
6StatusTotal ActionsOutstandingNext Date ActionAction Item
7Hybrid_Platforms 70%206/06/2023
8Modern_Workspace 0%0None
9Security 0%106/08/2023
10Digital_Enablement 0%0None
Dashboard
Cell Formulas
RangeFormula
D7:D10D7=E7
E7E7=(tbHybrid[[#Totals],[Status]]/(COUNTA(tbHybrid[Item])*2))
F7:F10F7=COUNTIF(tbActions,C7 )
E8E8=(tbModern[[#Totals],[Status]]/(COUNTA(tbModern[Item])*2))
E9E9=(tbSecurity[[#Totals],[Status]]/(COUNTA(tbSecurity[Item])*2))
E10E10=(tbDigital[[#Totals],[Status]]/(COUNTA(tbDigital[Item])*2))
H7:H10H7=IFNA(INDEX(tbActions[Action Date],MATCH(MIN(IF(tbActions[Pillar]=C7,IF(tbActions[Status]<>"Completed", ABS(tbActions[Action Date]-TODAY())))),IF(tbActions[Pillar]=C7,IF(tbActions[Status]<>"Completed",ABS(tbActions[Action Date]-TODAY()))),0)),"None")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H7:H10Cell Value="None"textYES
D7:D10Other TypeIcon setNO


CDW - Sample.xlsx
BCDEFGHI
6PillarStack TypeActionAction DateStatusActioned ByNotesId
7Hybrid_PlatformsPlatform SecurityCall Joe06/06/2023In ProgressabcMust Call joe to discuss securityHyb-2
8SecurityData SecurityCall Tim06/08/2023In ProgressabcMust call Tim regarding Data SecuritySec-1
9Hybrid_PlatformsStorage & Computetest08/06/2023CompletedaaHyb-2
10Total33
Actions
Cell Formulas
RangeFormula
C10C10=SUBTOTAL(103,[Stack Type])
I7:I9I7=CONCATENATE(LEFT([@Pillar],3),"-",COUNTIF([Pillar],[Pillar]))
I10I10=SUBTOTAL(103,[Id])
Cells with Data Validation
CellAllowCriteria
B7:B10List=ActionPillar
F7:F10ListNot Started, In Progress, Completed
C7:C9List=INDIRECT(B7)
 
Upvote 0
drawlings said:
I am using Excel 365
Click to expand...
Please add that to your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
Peter_SSs said:
Please add that to your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
Click to expand...
Done. Thanks
 
Upvote 0
drawlings said:
Thanks, I am using Excel 365. See the mini sheets below

CDW - Sample.xlsx
CDEFGHI
6StatusTotal ActionsOutstandingNext Date ActionAction Item
7Hybrid_Platforms 70%206/06/2023
8Modern_Workspace 0%0None
9Security 0%106/08/2023
10Digital_Enablement 0%0None
Dashboard
Cell Formulas
RangeFormula
D7:D10D7=E7
E7E7=(tbHybrid[[#Totals],[Status]]/(COUNTA(tbHybrid[Item])*2))
F7:F10F7=COUNTIF(tbActions,C7 )
E8E8=(tbModern[[#Totals],[Status]]/(COUNTA(tbModern[Item])*2))
E9E9=(tbSecurity[[#Totals],[Status]]/(COUNTA(tbSecurity[Item])*2))
E10E10=(tbDigital[[#Totals],[Status]]/(COUNTA(tbDigital[Item])*2))
H7:H10H7=IFNA(INDEX(tbActions[Action Date],MATCH(MIN(IF(tbActions[Pillar]=C7,IF(tbActions[Status]<>"Completed", ABS(tbActions[Action Date]-TODAY())))),IF(tbActions[Pillar]=C7,IF(tbActions[Status]<>"Completed",ABS(tbActions[Action Date]-TODAY()))),0)),"None")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H7:H10Cell Value="None"textYES
D7:D10Other TypeIcon setNO


CDW - Sample.xlsx
BCDEFGHI
6PillarStack TypeActionAction DateStatusActioned ByNotesId
7Hybrid_PlatformsPlatform SecurityCall Joe06/06/2023In ProgressabcMust Call joe to discuss securityHyb-2
8SecurityData SecurityCall Tim06/08/2023In ProgressabcMust call Tim regarding Data SecuritySec-1
9Hybrid_PlatformsStorage & Computetest08/06/2023CompletedaaHyb-2
10Total33
Actions
Cell Formulas
RangeFormula
C10C10=SUBTOTAL(103,[Stack Type])
I7:I9I7=CONCATENATE(LEFT([@Pillar],3),"-",COUNTIF([Pillar],[Pillar]))
I10I10=SUBTOTAL(103,[Id])
Cells with Data Validation
CellAllowCriteria
B7:B10List=ActionPillar
F7:F10ListNot Started, In Progress, Completed
C7:C9List=INDIRECT(B7)
Click to expand...
you have a few other tables in your calculations as well, tbHybrid, tbModern, tbSecurity, tbDigital. I am assuming the table you did post is tbActions
 
Upvote 0
awoohaw said:
you have a few other tables in your calculations as well, tbHybrid, tbModern, tbSecurity, tbDigital. I am assuming the table you did post is tbActions
Click to expand...
Yes it is only the tbActions table I am interested in as I am trying to figure out the best formula to retrieve the Id column like I've done with the next date.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Lookup Data table with multiple dates
Replies
3
Views
72
Joe4
Joe4
H
  • Question
Macro Help - Remove Excess Data in Cell and Convert Remaining Data to Date Format M/DD/YYYY
Replies
5
Views
189
Rick Rothstein
Rick Rothstein
O
  • Question
Count only one occurrence of all criteria being met
Replies
8
Views
448
KRice
K
S
  • Solved
Return Value based on max date
Replies
6
Views
235
Fluff
Fluff
S
  • Question
IF cells in column A contains a dates that are 7 days before todays date then output the date and the value in the coulumn next to it in a list
Replies
1
Views
392
etaf
etaf

Forum statistics

Threads
1,215,073
Messages
6,122,977
Members
449,095
Latest member
Mr Hughes

Share this page

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