SOLVED

# Excel - What formula do I use here?

Copper Contributor

# Excel - What formula do I use here?

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

6 Replies

# Re: Excel - What formula do I use here?

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.

# Re: Excel - What formula do I use here?

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!

# Re: Excel - What formula do I use here?

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?

# Re: Excel - What formula do I use here?

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

Thanks a lot

best response confirmed by WillRiversxx (Copper Contributor)
Solution

# Re: Excel - What formula do I use here?

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)

# Re: Excel - What formula do I use here?

Thankyou so much! Brilliant 🙂
1 best response

Accepted Solutions
best response confirmed by WillRiversxx (Copper Contributor)
Solution

# Re: Excel - What formula do I use here?

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)