0

Is there any way in Gmail's filtering mechanism to find e-mail messages received or sent:

  • in a given period of a day (each day -- only time component of date matters here),
  • in a given period of week (each week).

I need to list (among 26k e-mails) all messages sent or received during nights or weekends.

Any solution suits me:

  • Gmail: temporal filter -- list all matching e-mails in main view,
  • Gmail: permanent filter -- add specific label to all matching e-mails,
  • non-Gmail: any external tool or solution that can help me achieve above.

If this is possible in Gmail then how should I configure filtering parameters? If this is not possible then is there any tool or solution I should look after?

Improve this question

2 Answers 2

Reset to default
1

You can perform searching between two time periods of a day like this:

after:1552896000 before:1552924800

which will list all emails you received between 8:00-16:00 on 18. March 2019
(timestamps were acquired by using https://www.epochconverter.com/)

However, in your case, you will probably need to apply the time interval logic on the far greater time range than a 1 day for which you can try to import all your messages from your Gmail inbox into Google Sheets and then filter out stuff you need by simple QUERY formula. There are various tools and scripts for this task all over the place.

An example would be https://www.integromat.com/en
which comes with a handy tutorial: https://youtu.be/9VNFoxR43II

Improve this answer
1

Since (for some internal reasons) I couldn't use the suggestion, i.e. to upload messages from Gmail to Google Sheets and try to filter them there, I came with a semi-automatic, semi-manual way using Excel, which is also inspired by user0's answer.

To cut the long story short (details, discussion and alternative approaches are all here) what I did was:

  1. Fill tha A column with all the dates in desired range (1,5+ year, 610 dates / rows in my case).
  2. Set a fixed start time and end time and store it in some separate cells (C1 and E1).
  3. Generate dates with given start and end time:
    • start date: =DATE(YEAR(A4);MONTH(A4);DAY(A4))+TIME(HOUR($C$1);MINUTE($C$1);SECOND($C$1)),
    • end date: =DATE(YEAR(B4);MONTH(B4);DAY(B4))+TIME(HOUR($E$1);MINUTE($E$1);SECOND($E$1)).
  4. Convert these dates into Unix timestamp:
    • start date: =(B4-DATE(1970;1;1))*86400,
    • end date: =(C4-DATE(1970;1;1))*86400.
  5. Concatenate resulting values to get filtering string and URL:
    • filtering string: ="after:" & D4 & " before:" & E4,
    • URL: ="https://mail.google.com/mail/u/0/#search/after%3A" & E4 & "+before%3A" & F4.

And I have resulted with a long list of filtering strings and URLs, like:

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

ready to be pasted into Gmail's search box:

enter image description here

or into browser's address bar.

An example of the resulting sheet (get it here):

enter image description here

There are some more modifications to above to be considered that includes:

  • adding extra day when hours are passing midnight:

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

  • using whole day (00:00:00-23:59:00) when day is during weekend:

    • first change:
      • part: +TIME(HOUR($D$1);MINUTE($D$1);SECOND($D$1)),
      • with: +IF(WEEKDAY(A4;2)>5;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.
    • second change:
      • part: DAY(C4+IF($E$1<$C$1;1;0))
      • with: DAY(C4+IF(AND($F$1<$D$1;WEEKDAY(A4;2)>5);1;0))),
      • to not add extra day for weekend dates, even if time difference suggests passing midnight.
    • third change:
      • and part: +TIME(HOUR($F$1);MINUTE($F$1);SECOND($F$1))
      • with: +IF(WEEKDAY(A4;2)>5;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.

Note, that the above approaches solves "weekends" problem directly while original blog post) uses separate column instead (see screenshot above) and thus formulas are a bit different.

Sources:

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.