I wrote a makro for an Excel export of a third party tool.

Content:

• Column A lists all the check-in times
• Column B lists all the check-out times
• Lines are just all the days from a certain period

Target:

Check if the check-in time of the following day is later than the check-out time of the current day. So: =$A1>$B2 and so on.

Problem:

Lets assume the check-in time is always 8 am and the check-out time is always 6 pm. The times of the export are displayed like 8:00, 18:00, .. . In that case the output is always displayed wrong as true (I assume because the comparison is working unit by unit). If I click in the check-in field, the appearance changes to 08:00and the output of the formula changes to false.

Do you have any idea, why? Format of the cell remains untouched ([hh]:mm) for both.

CodePudding user response：

Your times are text, not times (which are just formatted numbers).

Use the double unary -- to coerce the text to a number:

=--$A1>--$B2