Excel - What formula do I use here?

[WillRiversxx]

Hi all,

Using the below information, I wanted to create a formula to automatically fill in the column E with the text I've placed in there 'Information needs to be updated' etc. when someone enters the incorrect date, or has insufficient information - I've done that part with no problem, however - i'm now stuck! How do I now calculate the difference between the two dates for the ones which are correct? It won't let me add this on as an combined formula, either that or i'm not doing it correctly! I've tried adding on =NETWORKDAYS(D2,C2) on the end or just simply =D2-C2 but won't calculate, just returns 'False'. 

What do I do here? I know it's possible, i'm just confused! Screenshot below:

Thanks so much

[#2]

@WillRiversxx 

Some of those dates look weird - year 1900, or 02.12

Try cleaning up the dates, then try

=IF(D2-C2<0, "Requires ... etc.", D2-C2)

and format the column as General.

[#3]

Yep, some of the data is terrible! Definately needs updating 😄 - I've tried this on a few other examples whereby the dates are correct (E.G. Row 2 / 3) but still returns as a #VALUE or #REF!

Completely stumped!

[#4]

@WillRiversxx 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#5]

@HansVogelaar - https://www.dropbox.com/scl/fi/f5inlsoclvenrycrpvwq5/Excel-Formula-issue.xlsx?rlkey=le9imzadkzlip2fn...

Here's the dropbox link if you think you can help! Would be fantastic. 

Thanks a lot

[#6 · Accepted solution]

@WillRiversxx 

If you set the horizontal alignment of columns C and D to General, you'll see that while most values are right-aligned, a few are left-aligned, indicating that Excel sees them as text values instead of as 'real' dates.

This will cause the formula to return #VALUE!

I have corrected these dates in the attached workbook, and changed the formula to

=IF(OR(D2="",D2-C2<0),"Requires contract award (planned) / actual date to be amended",D2-C2)

[#7]

Thankyou so much! Brilliant 🙂