Comparing Dates

[poucedeleon]

I have been tasked with moving data from a sharepoint calendar into a new training database. I am trying to identify all the rows that have more than one day. The following is an example of the format. The top three are multiple days, where the bottom row is one day. I would like to know if there is a conditional format that will identify the mutiple day rows. The single days will import fine, but I need to create seperate rows for all of the mutiple day rows, and I have about 3000 rows, so I am looking for a way to target just the rows that need work.

Training ID	Name	Start Date	End Date
093540	MIELE	2/10/2014 0:00	2/11/2014 23:59
110610	MOORMEIR	2/10/2014 0:00	2/12/2014 23:59
030621	CORNELL	2/11/2014 0:00	2/15/2014 23:59
048370	LARKIN	2/11/2014 0:00	2/11/2014 23:59

<tbody>
</tbody>

 

[Rick Rothstein]

I have been tasked with moving data from a sharepoint calendar into a new training database. I am trying to identify all the rows that have more than one day. The following is an example of the format. The top three are multiple days, where the bottom row is one day. I would like to know if there is a conditional format that will identify the mutiple day rows. The single days will import fine, but I need to create seperate rows for all of the mutiple day rows, and I have about 3000 rows, so I am looking for a way to target just the rows that need work.

Training ID	Name	Start Date	End Date
093540	MIELE	2/10/2014 0:00	2/11/2014 23:59
110610	MOORMEIR	2/10/2014 0:00	2/12/2014 23:59
030621	CORNELL	2/11/2014 0:00	2/15/2014 23:59
048370	LARKIN	2/11/2014 0:00	2/11/2014 23:59

<tbody>
</tbody>

Select Column C and D from Row 2 on down to the last row you ever expect to have data in, then call up the Conditional Formatting dialog box and use this formula for the condition...

=INT($C2)<>INT($D2)

 

[poucedeleon]

That works perfectly, Thank you very much!!!

 

[poucedeleon]

Rick, would it be asking to much to have you explain the formula. I think I understand part of it. <> looks to see if the data in the cells are equal, but I don't know what the $ sign does and I am not familiar with "INT"
=INT($C2)<>INT($D2)

 

[Rick Rothstein]

Rick, would it be asking to much to have you explain the formula. I think I understand part of it. <> looks to see if the data in the cells are equal, but I don't know what the $ sign does and I am not familiar with "INT"
=INT($C2)<>INT($D2)

Dates, to Excel, are floating point numbers (the date format you see is only for the convenience of the human viewer)... the integer part is the number of days offset from some "date zero", so Jan 1, 1900 is offset 1, Jan 2, 1900 is offset 2, on up to today (Feb 21, 2014) which is offset 41691... the decimal part is the fraction of a 24-hour day that the time part of the date represents. Since you showed the dates with times, it was necessary to remove the time part for the comparison... the INT function removes the decimal part of a positive floating point value.

 

[poucedeleon]

Dates, to Excel, are floating point numbers (the date format you see is only for the convenience of the human viewer)... the integer part is the number of days offset from some "date zero", so Jan 1, 1900 is offset 1, Jan 2, 1900 is offset 2, on up to today (Feb 21, 2014) which is offset 41691... the decimal part is the fraction of a 24-hour day that the time part of the date represents. Since you showed the dates with times, it was necessary to remove the time part for the comparison... the INT function removes the decimal part of a positive floating point value.

Again, Thank you that was very helpful!!!