Excel Newbie

[Zad1107a]

Hi everyone,
Im trying to go above and beyond by making an inventory sheet for work. I want excel to highlight the expiration dates of items that will expire in less than 30 days but I have no idea to write the formula(s). If I write out what I want to have happen it looks something like this: IF(Date-Today)=<30 then highlight.

Thanks in advance!!

 

[Zad1107a]

Ah, I think I have it backwards. If these are future dates, we should be subtracting the current date from the existing date (sometimes it really helps to see the examples!).
So try switching those values around, i.e.

=D12-TODAY()<=30

Note that if you may have some blank values in column D that you do NOT want highlighted, we will need to add another check, i.e.

=AND(D12>0,D12-TODAY()<=30)

that did it thank you!!!

 

[Joe4]

Regarding the follow-up you sent in your PM to me:

In that example I provided I had to remove some text in order to get the formula to work. Several of the medications I am inventorying have multiple quantities with the same expiration date. Originally I had the quantities included in the cell ie: 10/25/2019 (x2). I simply moved them over to where I had the overall quantity of that specific drug but now I'm running into the same problem when I'm trying to make a formula to highlight when the required amount is less than the current stock. Ideally I would like to keep the quantity with that particular expiration with the dates for ease of use. Is there anyway I can have the formula ignore "(x2)"

Assuming that all entries have the format "Date Space Quanity", try this:

=AND(D12<>"",DATEVALUE(LEFT(D12,FIND(" ",D12)-1))-TODAY()<=30)

 

[Zad1107a]

Regarding the follow-up you sent in your PM to me:


Assuming that all entries have the format "Date Space Quanity", try this:

=AND(D12<>"",DATEVALUE(LEFT(D12,FIND(" ",D12)-1))-TODAY()<=30)

Thanks for the reply, didn't see the notification in my email. The exact way I would prefer to document the quantities specific to an expiration date would be: Expiration date (xQuantity.) Example 11/2/2019 (x2) indicating that there are two units that expire on 11/2/2019 (with the remaining units expiring on a different date.)

 

[Joe4]

Thanks for the reply, didn't see the notification in my email. The exact way I would prefer to document the quantities specific to an expiration date would be: Expiration date (xQuantity.) Example 11/2/2019 (x2) indicating that there are two units that expire on 11/2/2019 (with the remaining units expiring on a different date.)

I am not sure I undersrtand what you are saying. My solution should work for an entry like: 11/2/2019 (x2)