Date format not recognised in Countifs Formula.

[Chris Waller]

Dear All,

I am trying to analyse some data in Excel that I have downloaded from Access. I get a File error message saying some number formats may have been lost. I suspect that it may be something to do with the date fields, because when I try to create a countifs formula in Excel, the formula does not pick up any information. I have been informed that I need to create an = Value formula to pick up the dates and change them into values. I don’t know if I need to do something in Access to stop this happening (my Access knowledge is more limited than my Excel knowledge) or whether it would be better to create a macro that could identify the dates as they are in a dd/mm/yyy format and do something so that they are all identified and changed in so that the formula can identify them as dates. I currently have a 158 records and there are potentially 9 columns of dates and the number of records is increasing by the day. I would appreciate it if someone could help provide the best\quickest solution. TIA

 

[CROY1985]

just try =datevalue(a1) where A1 is where the date is held. You can then copy, paste special values only, over the top of the original dates.

 

[konew1]

The dates coming from access are in dd/mm/yyy (maybe dd/mm/yyyy) format, but what is the default date format for your excel?

If you make a formula to add 1 to the dates do you get the next day?

Are the dates a field with date and time, formatted to show date only? That could be messing with your countif. If you are setting a condition of a specific date you will get nothing returned. If you are setting a date range you could get unexpected results.

 

[konew1]

just try =datevalue(a1) where A1 is where the date is held. You can then copy, paste special values only, over the top of the original dates.

You need to make a range of the same dimensions as the data range and copy that for paste special values