Forum Discussion
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)
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.
Completely stumped!
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?
HansVogelaar - https://www.dropbox.com/scl/fi/f5inlsoclvenrycrpvwq5/Excel-Formula-issue.xlsx?rlkey=le9imzadkzlip2fnvjwykwjz2&dl=0
Here's the dropbox link if you think you can help! Would be fantastic.
Thanks a lot
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)