Find all e-mails sent or received in Gmail after office hours

Someone gave me a task to find all e-mail messages sent or received in Gmail:

  • in given period of day — after 16:00 given day and before 08:00 the next day and
  • in given period of week — between 00:00 and 23:59 during weekends,

so, generally speaking, outside office hours.

Gmail has a quite powerful filtering mechanism, but it:

For some internal reasons I couldn’t follow the “automatic way” suggestion (i.e. to upload messages from Gmail to Google Sheets and try to filter them there) and thus I decided into semi-automatic way.

Meaning that I wrote an Excel sheet which generated Gmail’s filtering queries for me for all required dates and I only have to manually copy-paste them into Gmail’s search box.

You can get similar results Epoch Converter’s batch tool, but I wanted to play a little bit with Excel instead.

Preparation

As for the basic steps, I have:

  1. Started (since I wanted to be a bit flexible) with defining two cells for start and end hours (C1 to F1; see below).
  2. Added some explanatory header row with some labels in third row (A3 to F3).
  3. Filled first column with date range; in my case it was 610 dates from 01.06.2017 to 31.01.2019 (A4 to A613).
  4. Formatted these dates using custom [$-14809]dd.mm.rrrr;@ format to get dates in above mentioned format.

Start and end dates and times

For start date expressed as date-time we have (for B4 cell and then on-ward, until B613 in my case):

  • base date is fixed and stored in A4,
  • we need to get this date in numeric form — DATE(YEAR(A4);MONTH(A4);DAY(A4)),
  • fixed start time is kept in C1, so we use a permanent addressing with $C$1,
  • we need to take this time in numeric form — TIME(HOUR($C$1);MINUTE($C$1);SECOND($C$1)) and
  • we need to add both values together.

Thus, final version of B4 cell’s formula is:

=DATE(YEAR(A4);MONTH(A4);DAY(A4))+TIME(HOUR($C$1);MINUTE($C$1);SECOND($C$1))

For end date expressed as date-time we have (for C4) the same algorithm with only small exceptions:

  • base date is calculated in B4,
  • fixed end time is kept in E1 and we again use a permanent addressing,

Thus, final version of C4 cell’s formula is:

=DATE(YEAR(B4);MONTH(B4);DAY(B4))+TIME(HOUR($E$1);MINUTE($E$1);SECOND($E$1))

Start and end timestamps

Gmails uses dates in timestamp (see Epoch Converter) and we need to convert Excel dates to this format.

This isn’t pretty much hard task (source):

  • start date is calculated in B4 and already in correct format,
  • but it counts since 1 January 0001 not since 1 January 1970 (Unix Epoch),
  • thus we need to subtract these two values: B4-DATE(1970;1;1) and
  • it is expressed as number of days that passed (25569 days till Unix Epoch and 43545 days till today),
  • while we need it in seconds for timestamp — *86400.

The whole formula for D4 cell (and on-ward) is as simple as:

=(B4-DATE(1970;1;1))*86400

To express end date as a timestamp we use exactly the same formula, but based on end date (E4 cell) instead:

=(C4-DATE(1970;1;1))*86400

Concatenation

And now some “gluing” using Excel’s concatenation operator (source), in F4 cell:

="after:" & D4 & " before:" & E4

Thanks to above, we get a string in form of:

after:1496419200 before:1496390400

Which can be pasted directly into Gmail:

And the final Excel sheet looks like this:

Possible modifications

I have added new (B) column with =IF(WEEKDAY(A6;2)>5;"Y";"N") formula to denote days during weekends (source). I have also added conditional formatting to this column, so “Y” for weekends is printed as white on a red background.

For the column holding end date expressed as date and time (D4 after adding B4 above) I have changed:

  • the DAY(B4) part into
  • DAY(B4+IF($E$1<$C$1;1;0)).

This is to increase day count if end hour is earlier than start hour (assuming night; passing midnight). Especially useful since we are talking about finding e-mails sent or received during nights.

The whole formula for D4 cell and onward is thus:

=DATE(YEAR(B4);MONTH(B4);DAY(B4+IF($E$1<$C$1;1;0)))+TIME(HOUR($E$1);MINUTE($E$1);SECOND($E$1))

Finally, I have added new, H column with formula (another concatenation):

="https://mail.google.com/mail/u/0/#search/after%3A" & E4 & "+before%3A" & F4

to generate URLs-like strings in form of:

https://mail.google.com/mail/u/0/#search/after%3A1496350800+before%3A1496376000

ready to be pasted into browser’s address bar instead of Gmail’s search box. Can be useful sometimes.

Final Excel sheet

Final version of my sheet, after all above modifications, looks like this:

and can be downloaded here: http://onezeronull.com/files/2019/03/Find-all-e-mails.xlsx.

Fixture for weekends

I haven’t addressed second requirement — to find all e-mails, irrespective of time, sent during weekends.

Fortunately, this is quite easy to be achieved:

  • for Start (date + time) (C4) cell change:
    • part: +TIME(HOUR($D$1);MINUTE($D$1);SECOND($D$1)),
    • with: +IF(B4="N";TIME(HOUR($D$1);MINUTE($D$1);SECOND($D$1));TIME(0;0;0)),
    • to ignore fixed start time and “reset” start hour to midnight (00:00:00) during weekends.
  • for End (date + time) (D4) cell change:
    • part: DAY(C4+IF($E$1<$C$1;1;0))
    • with: DAY(C4+IF(AND($F$1<$D$1;B4="N");1;0))),
    • to not add extra day for weekend dates, even if time difference suggests passing midnight,
    • and part: +TIME(HOUR($F$1);MINUTE($F$1);SECOND($F$1))
    • with: +IF(B4="N";TIME(HOUR($F$1);MINUTE($F$1);SECOND($F$1));TIME(23;59;59))
    • to ignore fixed end time and “reset” end hour to near midnight (23:59:59) during weekends.

Unfortunately, above change is not depicted in screenshot and is not added to final version of downloadable Excel sheet, so you will have to do it yourself, if you need it.

Semi-automation?

When you finally get done with search / filtering strings like:

after:1496419200 before:1496390400

and you prefer to use them (rather than full URLs) then, please, note, that you can combine more than one of such strings into pairs or even complex queries to search for given period of the day in more than one day, i.e.:

(after:1496419200 before:1496390400) OR (after:1551834000 before:1551913200)

According to this comment, the limit is 1894 characters per query, which means that you can combine up to 45-46 days in one filtering query, assuming that you have only one time-period per day to look for:

This allows you to at least think about some semi-automation of such process.

For example, I have followed these steps:

  1. Copied results of the G column from before-mentioned Excel sheet into Notepad++ (30 entries per whole month).
  2. Hit Ctrl+H and selected replace options:
    • Find what: \r\n,
    • Replace with: ) OR (,
    • Search mode: Extended (\n, \r, \t, \0, \x...).
  3. Replaced all occurrences.
  4. Added missing ( in the beginning of first line and ) in the end of last line.
  5. Pasted resulting string into Gmail.

The effect:

(after:1504288800 before:1504337400) OR (after:1504310400 before:1504396799) OR (after:1504396800 before:1504483199) OR (after:1504548000 before:1504596600) OR (after:1504634400 before:1504683000) OR (after:1504720800 before:1504769400) OR (after:1504807200 before:1504855800) OR (after:1504893600 before:1504942200) OR (after:1504915200 before:1505001599) OR (after:1505001600 before:1505087999) OR (after:1505152800 before:1505201400) OR (after:1505239200 before:1505287800) OR (after:1505325600 before:1505374200) OR (after:1505412000 before:1505460600) OR (after:1505498400 before:1505547000) OR (after:1505520000 before:1505606399) OR (after:1505606400 before:1505692799) OR (after:1505757600 before:1505806200) OR (after:1505844000 before:1505892600) OR (after:1505930400 before:1505979000) OR (after:1506016800 before:1506065400) OR (after:1506103200 before:1506151800) OR (after:1506124800 before:1506211199) OR (after:1506211200 before:1506297599) OR (after:1506362400 before:1506411000) OR (after:1506448800 before:1506497400) OR (after:1506535200 before:1506583800) OR (after:1506621600 before:1506670200) OR (after:1506708000 before:1506756600) OR (after:1506729600 before:1506815999)

And an ability to search entire month for all e-mail messages sent or received after office hours.

Leave a Reply