Question on Excel Dates

[Jennifer_Navy]

I have a formula this is pulling from a SQL query as 1 datasource and Excel as the other.

       =COUNT(IF(F$20=tbl[EntryDate],IF(tbl[InitRvwMetYN]="Yes",1)))

I should be getting data so I dig a little deeper and see this: 

My question is HOW is Excel creating the .25 after the date and HOW can I get rid of it?  My formula is never going to work if I can't match up the dates.

• I have went into my source table and changed the format to  Date. (didn't help)
• I have formatted the date to Number and removed the 2 decimal places (didn't help)

I am running out of ideas on a Friday night. Any help would be much appreciated. 

-Jennifer

[#2]

It appears the values are date/time (time being the decimal portion). Perhaps try using INT to separate only the date. Maybe the data source is a date/time field and uses 6:00 am as a default value for the time?

=COUNT(IF(F$20=INT(tbl[EntryDate]),IF(tbl[InitRvwMetYN]="Yes",1)))

[#3]

Be clear on one thing:
Re: "My question is HOW is Excel creating the .25"
Jennifer!
Excel is not creating anything! That .25 must be in your data.
Before you start to analyze your data you should extract it and examine it.
Once you do that, you'll be able to look at extracting what you want and then start analyzing it.
Norman

[#4]

@Jennifer_Navy 

In addition, changing the format doesn't change the value kept in the cell. It only shows it to you this or that way.

[#5]

Absolutely!

Just because your database only shows numbers in the excel date serial numbers from 0 (0-Jan-1900) through to 2958465 (31-Dec-9999) doesn't mean to say that is what is stored.

That database of yours might have been created automatically at 6:00AM (0.25) and formated to just show the integer.

I may well 'sit to be corrected' but I can't think of any situation where Excel has done anything more than import or show what is there to be shown.

Coincidentally, I did a posting on this very topic on 5th Mar 2021 (AET) on the 'Excel Modeling for Commercial Real Estate Professionals' LinkedIn group. If you join that group you should be able to see an example how each item in one field was integrity checked for the 'rules' applicable to that item of data.