PDA

View Full Version : Solved: Understanding Date and Time Formats



Opv
05-13-2013, 07:33 AM
I have formatted a column which displays a combined date and time format, i.e., 5/13/2013 9:12 AM, and I have no problem with the entries being displayed as desired. However, I am losing some functionality when it comes to testing whether cells in that column fall on a particular date. The same is true with respect to testing whether the cells reflect AM hours or PM hours. My formulas worked so long as I had the dates/times in separate columns; however, once I combined the dates/times in the same cell my formulas stopped working. For example, presuming TODAY is 5/13/2013 and that the above date/time is in cell A1, the simple formula =IF(A1=TODAY(),"True","False") results in False, even though it is true.

Is there a way to test for particular dates and date ranges, as well as for AM vs PM entries without having to break dates/times into separate cells?

p45cal
05-13-2013, 08:56 AM
=INT(A1)=TODAY() will be true if it's the same date
=MOD(A1,1)>=0.5 Will be TRUE if it's afternoon false if AM (or =HOUR(A1)>=12 )

Opv
05-13-2013, 09:02 AM
=INT(A1)=TODAY() will be true if it's the same date
=MOD(A1,1)>=0.5 Will be TRUE if it's afternoon false if AM (or =HOUR(A1)>=12 )

Indeed they do. Thanks.