One of the main issues when I worked with colleagues based in different time zones, it is the hour, for instance, America, Europe, Africa and Asia. In our common reporting files, we put comments so when I finished my day, my colleagues can take over and keep going on based on my feedback but for 1 particularly report, we needed something more helpful in an automatic way.

The report was about important incidents happened during the day, and as you know, a day lasts 24h. Although important incidents are less than simple incidents, I needed a quick way to identify and to tell them which ones they have to follow and when I will be back in the office, to know which ones I have to take care.

When I use the formula ?

When I need to know if an important issue has to be followed during my business hour.

How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

How is/are the formula(s) ?

=IF()

=OR()

=ISBLANK()

=VLOOKUP()

=ISTEXT()

=ISNA()

=TODAY()

The Sheet1 is where you put the formula to check if the important incident is still opened and the Sheet2 is an history so the formula can compare between today (Sheet1) and yesterday (Sheet2).

This formula is just taking in account the “end” field, nothing more.

=IF(OR(ISBLANK(D2),ISBLANK(VLOOKUP(B2,Sheet2!B:D,3,0))),"to follow","not follow")

This formula is taking in account the “end” and “comment” fields because if it is a new incident, there is no comment yet.

=IF(ISTEXT(A2),IF(OR(ISBLANK(D2),ISBLANK(VLOOKUP(B2,Sheet2!B:D,3,0))),"to follow","not follow"),IF(ISBLANK(A2),"to follow","not follow"))

This formula is taking in account the “ID” and “end” fields, also comparing the today date so if my colleagues from other time zone put a comment, I will still know which one is new in my time zone.

=IF(ISBLANK(D2),"to follow",IF(ISNA(VLOOKUP(B2,Sheet2!B:D,1,0)),"to follow",IF(TODAY()<F2,"to follow",IF(ISBLANK(VLOOKUP(B2,Sheet2!B:D,3,0)),"to follow","not follow"))))

This formula is taking in account the “ID” and “end” fields, also comparing the today and yesterday date. As you can see, there are 2 columns of “take care”, it is useful if you want to take in account the local time of your colleague. For example, when I open the file, the date/time will show in CET and when an US colleague opens it, it will show in EST so based on that, the result will be different for the 2 time zones.

=IF(ISBLANK(D2),"to follow",IF(ISNA(VLOOKUP(B2,Sheet2!B:D,1,0)),"to follow",IF(E2>Sheet2!$E$2,"to follow",IF(TODAY()<F2,"to follow",IF(ISBLANK(VLOOKUP(B2,Sheet2!B:D,3,0)),"to follow","not follow")))))

NOTE: for “take care EST”, just change “Sheet2!$E$2” by “Sheet2!$F$2”