multiple date function

C

CLALANNE

New Member
Joined
Oct 13, 2023
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
Hello,
I have this table with different dates and want to know how to make if/and/or function formula for it. The dates within 30 days should say Expiring. The day of and up to 13 days pass is Expired. 14 past the expiration date is on hold. Those within compliance are Approved.
1697470678151.png
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
Welcome to the Board!

I think we need more explanation, as you have multiple columns with dates in them. On any given rows, sometimes the dates are the same, sometimes they are not.
So how do we know which date to use for the calculation?
 
Upvote 0
I used my own dates as I'm not going to type all those dates verbatim, I could make errors. But you can use this as a template. I also am not using the TODAY() function I am entering a target date. You can change that as you see fit.

But, does this work:
I have highlighted the earliest date(s) on each row.


Mr excel questions 67.xlsm
ABCDEFG
1Date to Assess2023-10-15
2
3COI StatusCGL ExpAuto ExpEsc & Umb ExpProf ExpCargo ExpW C Exp
4On Hold2023-12-312024-04-052023-09-072024-01-072024-03-192024-04-02
5Expiring2024-01-102023-12-312023-10-252024-01-032024-02-082024-03-04
6Expiring2023-12-162024-04-022024-03-222024-01-222024-04-072023-11-06
7Expiring2024-01-042024-03-302023-12-222023-11-162023-11-072024-03-08
8Expired2024-01-022023-12-202024-01-112023-12-172023-12-182023-10-05
9Expired2023-12-082024-02-162024-03-312024-01-222023-11-242023-10-13
10Approved2024-03-262023-12-172024-01-242023-12-172024-03-042023-12-22
11Expired2023-12-162023-10-062024-01-312024-02-272024-03-022024-01-15
12Expiring2023-12-222023-11-062023-12-282023-12-172024-01-062024-01-28
13Expiring2024-01-182024-02-182024-03-012023-11-072024-04-032023-10-31
14Expiring2023-12-302024-02-092023-12-202024-02-232023-10-212024-03-05
Sheet4
Cell Formulas
RangeFormula
A4:A14A4=IF(MIN(B4:G4-$B$1)<=-14,"On Hold", IF(MIN(B4:G4-$B$1)<=0,"Expired", IF(MIN(B4:G4-$B$1)<30,"Expiring","Approved")))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B4:G14Expression=B4=MIN($B4:$G4)textNO
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

Y
  • Question
Conditional formatting
Replies
3
Views
121
SanjayGMusafir
SanjayGMusafir
S
  • Solved
Copying a row to another tab when condition is met on expiry date
2
Replies
16
Views
739
kevin9999
K
R
  • Question
Popup to Notify of Expiring/Expired Dates
2
Replies
11
Views
771
Herakles
H
L
  • Question
Using DATEDIF with multiple date criteria
Replies
3
Views
312
Eric W
Eric W
S
  • Question
Using IFS to calculate a task due by date but needing to remove weekends
Replies
3
Views
91
awoohaw
awoohaw

Forum statistics

Threads
1,215,091
Messages
6,123,062
Members
449,090
Latest member
fragment

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