Hi
For anyone like me who needs to branch workflow actions according to what day of the week it is when the contact is in the workflow, while not having access to Ops Pro, this might be the solution for you. We use it for sales team members who aren’t in certain days of the week.
This is without code and only using the tools I have available - there are almost certainly smarter solutions, but this is one that works in Marketing Pro. At this level you can’t schedule workflows, necessitating this convoluted solution. It also means there’s a few hours where the data is wrong, but this shouldn’t affect new contacts.
Once I’ve laid out what works, I’ll explain how and why it works.
Note: All times and calculations are relative to UTC+0. I’ll explain how to adapt this to other timezones in the explanation at the bottom.
Step 1: ‘Today’s date’, ‘Today’s date as weekday’ and ‘weekday calculation’ custom properties
- Create a custom property for your contacts called ‘today’s date’ or similar. Set it as a ‘date picker’ - we don’t need the time as well.
- Create a custom property called ‘today’s date as weekday’ or similar. Set it as a ‘Dropdown select’, and place your days of the week Monday-Sunday each as a separate option. They don’t have to be in order.
- Create a custom property called ‘the beginning of time’ or ‘Unix epoch’ or ‘01/01/1970’. Set it as a ‘calculation’, ‘custom equation’, Output type ‘Date’. and this is the formula: (0). The brackets are unnecessary but still function. When you test the formula you should get the result 1/1/1970.
- Create a custom property called ‘weekday calculation’ or similar. I actually called ours ‘days between now and the beginning of time (Text)’ as that’s what it does, but it’s made it hard to find again later. This is a weird one which will become clear later on.
Set it as a ‘calculation’, ‘custom equation’, output type ‘String’. Then use this calculation:- number_to_string(((time_between (‘today’s date’, ‘The beginning of time’) / 86400000) / 7)
- ‘Today’s date’ and ‘The beginning of time’ are the properties you created in steps 2 and 3, so use the names you gave them. When you test this formula using 1/1/1970 as the beginning of time and any date as ‘today’s date’, you should get a result like this: 2879.142857
- number_to_string(((time_between (‘today’s date’, ‘The beginning of time’) / 86400000) / 7)
Step 2: Create two separate active lists
These lists are how the workflow will keep updating and setting ‘today’s date’ to be today. HubSpot limits what allows contacts to be re-enrolled in workflows and ‘today’s date hasn’t been updated’ is one that isn’t allowed.
These two lists are ‘today’s date is today’ and ‘today’s date is yesterday’. For the ‘today’ list, create it with the filter ‘Today’s date has been updated in the last 0 days’. For the yesterday list, give it the filter ‘today’s date has not been updated in the last 0 days’. These lists are now mutually exclusive, and can be used for triggering workflows and checking your system works.
Step 3: The Workflow
There might be unnecessary steps in here, but if it ain’t broke…
- Enrollment trigger: ‘Today’s date’ is unknown OR ‘Today’s date has not been updated in the last 1 day’ OR (something your contacts do frequently, like visit your website) OR ‘List membership’ is member of ‘Today’s date is yesterday’. The last one is critical going forwards, while the first three catch new contacts.
- Turn on Re-enrollment, tick every available option
- Action Edit Record: Set ‘Today’s date’ to the date this action was executed.
- Branch Branch based on filter criteria:
-
These can be in any order, but must be exactly as they are written here. The two strings are for UTC and UTC+1:
-
Friday: Weekday calculation contains any of [.142] [.148] Saturday: Weekday calculation contains any of [.285] [.291] Sunday: Weekday calculation contains any of [.428] [.434] Monday: Weekday calculation contains any of [.571] [.577] Tuesday: Weekday calculation contains any of [.714] [.720] Wednesday: Weekday calculation contains any of [.857] [.863] Thursday:Weekday calculation does not contain any of .ORWeekday calculation contains any of [.00595] Thursday does not contain a decimal
-
For each branch, create an Action ‘Set Today’s date as weekday’ to the weekday of the branch, like this:
-
For ‘none met’, create a Delay, choose ‘Until a specific time of day’ and choose 00:01. It does mean you’ve got a 1-minute window where contacts might have the wrong day of the week, but I see no alternative. Point every weekday branch to this delay, as above
-
-
Action (we are now only on the ‘None met’ branch) Edit record: Set ‘Today’s date’ to the date this action was executed.
-
OPTIONAL Delay, choose ‘For a set amount of time’ and then 5 minutes. I have this to give the system time to run the calculation, but it might not be necessary. This expands the window of ‘wrong day of the week’ to 6 minutes.
-
Branch Copy the branch from before (checking for ‘weekday calculation’), and copy the ‘Set Today’s date as weekday’ actions. Now the workflow can end and you can set it to live.
-
- Like me you’ll want to leave it a week or so before you do anything with the day of the week property this should give you. Check your lists are working as expected (‘today’s date is today’ should have every contact in it, ‘today’s date is yesterday’ should only have a handful at any one time). In a week check a random older contact that their weekday is updating daily. It should look like this when you click ‘Details’:
You’ll notice that the time the weekday was updated is sometimes over an hour after midnight. This is probably because there are tens of thousands of contacts this workflow is processing every day. This might not be smart.
And there you have it! If you’re interested, below is an explanation of how this works:
The explanation
Hubspot stores dates - whether date and time or just date - as milliseconds. This is the Unix standard, and Unix’s standard starts on 1/1/1970, a Thursday, and represents it as ‘0’. So every date in Hubspot/Unix is the number of milliseconds between 1/1/1970 and that date.
There are 86,400,000 milliseconds in a day, meaning you can divide a date represented as milliseconds by 86400000 to give you the number of days that have passed since 1/1/1970. Divide it by 7 after that and you have the number of weeks - a whole number indicates a whole week, meaning we’re back to Thursday.
For each day of the week, it’s always the same percentage of the week that’s passed since Thursday, meaning the decimals of the calculation are on a repeating cycle. Friday is always .142857 of a week, Saturday .285714 of a week, and so on.
We can use these decimals to know what day of the week any given Unix date is, which is why we need the ‘days between’ calculation from the unix epoch to ‘today’. You could probably just use ‘0’ but I want people who come after me to know why it’s 0 without knowing what the unix epoch is. The calculation outputs a ‘String’ because Hubspot workflow branches won’t allow you to filter by digits in a number, but it will for digits in a string, or ‘text’.
The two lists, while useful for checking, are what makes the workflow run over and over again. As soon as a contact hasn’t had their day of the week updated in 24 hours, they’re updated again because they move into the ‘yesterday’ list.
Adapting for timezones
You’re going to want Excel for this, or similar table programme for calculations.
I worked out the decimals for Monday, Tuesday etc by laying out my Hubspot Calculation as an Excel calculation. A fixed cell of 1/1/1970 00:00, then a range of dates, a calculation of the days between, divided by 7, to then lay out the repeating pattern.
If you change your fixed cell to 1/1/1970 01:00 for example, you’ll see the repeating pattern for UTC+1. Which is this:
| UTC+1 | Friday | 0.148 |
|---|---|---|
| Saturday | 0.291 | |
| Sunday | 0.434 | |
| Monday | 0.577 | |
| Tuesday | 0.720 | |
| Wednesday | 0.863 | |
| Thursday | 0.0059 |
Or more specifically, this:
Play around with the HubSpot calculation to check you get the same output. Good luck!
