Date Formula Confirmation (Is this the correct formula?)

[Arts]

Hi all

I have a list of dates for which projects are due to end, what I am trying to achieve is to highlight any date that is within a month of the project end date.

After searching around I think the E DATE function with TODAY are the functions to use,

I have ended up with the below formula which seems to work but I'm not sure if this is correct? If someone could please confirm if I'm on the right track that would be most appreciated

=IF(EDATE(H8,-1)<TODAY(),"Danger","ok")

Project End Date	Status
Sep-23​	ok
May-23​	Danger
Jun-23​	ok
Mar-24​	ok
Jun-23​	ok
Feb-24​	ok
Mar-25​	ok

Many Thanks

 

[etaf]

edate(H8,-1) will give you the first day of the previous month

you mnay want to add a H8<>"" other wise you will also flag empty cells as DANGER - because blank cells are seen as 0/1/1900 and so less than today

=IF(AND(H8<>"",EDATE(H8,-1)<TODAY()),"Danger","ok")

if you have say
21/9/23 - NOT sure from the image what the date is - or if its formatted to show month year or if text
then edate() will show
21/8/23

if you want the end of a month
then
EOMONTH()

so EDATE() will give a month

I prefer to use
DATE(Year(H8), Month(H8)-1,day(H8) )

then i can add in -1 month or +1 month or x number of days

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[Arts]

Thanks for the quick response,

I'm at work so restrictions with downloading anything would be a no go

The data above is pretty much what I have (though hundreds of rows more), what is throwing me is under May 23 where I have June 23 if I was to apply conditional formatting this would not get highlighted, I guess what I am trying to say is how is the formula calculating only to highlight when it is a month out.

From my understanding (limited) the E Date formula with the -1 is giving me a date a month before the projected end date how does it know this is the date to conditional highlight from ?

Thanks for the tip around blank cells

Also I see you have used an AND function and not an OR, wouldn't I need one of those to be true ? ie if the cell is blank OR if the date was approaching a month from the Projected End Date

I have used screen shots as when I copied and pasted the data the highlighted row (may 23) not being pasted as highlighted

 

[etaf]

whats is the actual date DAY in the may-23 the day...

if its > 12-may - then that will not highlight as edate will give 12th April from today

Also I see you have used an AND function and not an OR,

No you DO NOT want the cell to be blank - AND less than today - NOT OR

 

[Arts]

whats is the actual date DAY in the may-23 the day...

if its > 12-may - then that will not highlight as edate will give 12th April from today


No you DO NOT want the cell to be blank - AND less than today - NOT OR

The actual date is 01/05/23

Understood around the AND- took me a second !

 

[etaf]

so 1/5/23 in edate
would be the
1/4/23
and so that is less than today() and should highlight
providing is actually a real date and not text

Book3
	A	B	C	D	E	F	G	H	I
1								Project End Date	Status
2								9/1/23	ok
3								5/1/23	Danger
4								6/1/23	ok
5								3/1/24	ok
6								6/1/23	ok
7								2/1/24	ok
8								3/1/25	ok
Sheet1

Cell Formulas
Range	Formula
I2:I8	I2	=IF(AND(EDATE(H2,-1)<TODAY(),H2<>""),"Danger","ok")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2:H8	Expression	=AND(EDATE(H2,-1)<TODAY(),H2<>"")	text	NO

 

[Arts]

That is so surreal you explaining it like you did in your last post with the dates listed out seemed to have given me the clarity I needed to see it!!!

Thanks for your patience in explaining the obvious to me!

 

[etaf]

no problem, you are welcome

 

[Arts]

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

For some reason this was missing when I replied, not important now (for this post) but incase you may need to change a setting or for what ever reason this was showing as an image on your machine the above was data pasted in from a spreadsheet, so you should have been able to paste into excel. I attempted once again to do so and seems to work for me.

 

[etaf]

yes, it was listed as a table , not as an image - - BUT still has issues - and columns are not formatted correctly for being able to see formulas or dates formatted - so in order to use - leaves a lot of work to do

for example = the dates come across as text and so each one needs to be edited to be able to use as a date and then the formula added - also the column and rows are unknown ....... so can be guess work

XL2BB is much better as you can copy and paste into excel and see in excel , including any formatting and formula

BUT
I understand the issue here

I'm at work so restrictions with downloading anything would be a no go

and was happy to do the additional work - in order to get to a solution for you