so we have 3 senarios
Actual Date
Approx date
Year
As dates are just numbers you could go with a simple fix for the year and actual date
=IF(C2<2500,TEXT(C2,"@"),TEXT(C2,"mmm yyyy"))
If the number in the data column is less than 2500 it must be a year, so just keep as is. Other than that it is a date so we format as MMM YYYY
Working out if the date is approximate or actual date is going to be much harder - you could do an if on the day and say that if the day is 1 then it must be an approx date but this isnt 100%
=IF(C2<2500,TEXT(C2,"@"),IF(DAY(C2)=1,TEXT(C2,"MMM YYYY"),TEXT(C2,"DD mmm yyyy")))
Let me know if its any help
|