Working with dates in EasyMorph - Part 3

Having read the first two parts in this series, you should hopefully have a good understanding of dates and times in EasyMorph, how they are stored, and how to perform the most common manipulations and calculations using them. Let's now look at some of the less common and more complex topics for dealing with, and manipulating, dates and timestamps.

Days of the week

Some times we just need to know the day of the week a date falls on. For example, orders placed Sunday to Thursday may be dispatched the next day, but those placed Friday or Saturday are shipped the following Monday. If we wanted to handle this logic and calculate the dispatch date, we need to know what day of the week the order date falls on.

EasyMorph has two functions that can help us with this. The first is the weekdayname() function which is passed a date and returns the abbreviated (3 letter) name of the day of the week the date falls on (i.e. "Sun", "Mon", "Tue", "Wed", "Thu", "Fri" or "Sat"). The weekdayname() function is also passed a date but instead returns a number from 1 (Sunday) to 7 (Saturday).

So why two weekday functions rather than just one? The weekdayname() function is best used when you wish to display the day of the week. "Tue" is much simpler for us to understand than "3". But the weekday() function can be best used when we want to build logic based on the day of the week. If we take the above dispatch date use case, being able to write:

if(weekday([Sales Date)<= 5, ...)

Is much cleaner and more efficient than having to write:

if(weekdayname()="Sun" or weekdayname()="Mon" or weekdayname()="Tue" or weekdayname()="Wed" or weekdayname()="Thu", ...)

As well as the weekday() and weekdayname() functions, there is another which can further simplify logic expressions. The isweekend() function is passed a date and returns a boolean (TRUE or FALSE) depending upon whether the date falls on a weekend (Saturday or Sunday) or not. You might assume there is also a isweekday() function to perform the inverse, but there isn't. Instead, we can pair the isweekend() function with the "NOT" operator to work out if a date falls on a week day.

Work days

In part 2 of this series, we looked at how to calculate the number of days between two dates. As a brief reminder, assuming we have a date stored as a numeric value, we can simply take one from the other. However, another common use case which is related to the days of the week, is the need to work out how many work days there are between 2 dates.

Yet again, EasyMorph has a function to allow us to easily perform this calculation. The workdays() function is passed two dates and returns the number of weekdays (Monday to Friday) between the them.

If at all confused by this, you can consider the below examples, comparing them to a calendar:

Finding start or end dates

Knowing the start and end of some common date ranges can be important. As an example, I may need to pro rata a value based on the number of days left until the end of the year. To calculate the number of days between the dates in my data and the end of their corresponding years, I need to know the date the year ends on, and I can then subtract one from the other. You may initially think that this is an easy problem to solve using some of the things we've covered previously in this series, and for a year as in this example, you'd be correct.

Because a year always starts on January 1^st and ends on December 31^st, we can use the year() and makedate() functions to create dates for the start and end of the year as follows:

Unfortunately, this is where things stop being so simple. Whilst a month always begins on the 1^st, they can end on the 28^th, 29^th, 30^th or 31^st. We could probably write some logic for this, checking which month the date is in and taking that into account. But leap years throw us a curve ball, and if I need to know the start or end of a week, the problem gets even bigger.

Thankfully, the EasyMorph dev team have solved these problems for us already and provided us with functions to make our lives easier:

There is one last commonly experienced issue when dealing with the start and end of weeks, that the above function don't cover. In most countries, including here in the UK, a week traditionally begins on a Sunday and finishes on a Saturday. But some countries, international standards and businesses consider a week begins on a Monday and ends on a Sunday.

Both the weekstart() and weekend() functions assume the traditional Sunday to Saturday week, so we must use some simple logic to adjust if we need to know the start and end for a week beginning on Monday for example. The below table shows how this offset can be applied:

In both of these examples, we are subtracting a day from the week start and end, except if the date falls on a Saturday, in which case we add 6 days to the week start or end. You don't really need to worry about why Saturday gets this specially treatment, just know that it is needed.

Fiscal periods

For some reason known only to themselves, the finance world can't seem to operate on the same calendar as everyone else. Here in the UK, it is normal for companies to have a financial or accounting year that begins on the 1^st of April and ends on the 31^st of March the following year. And our government tax authority (HMRC) takes it one step further, wanting to know what you got paid between April 6^th and April 5^th of the following year.

This means that it is almost inevitable that at some point you will need to know the financial (also know as accounting or fiscal) year, quarter or month that a date falls within. We can't cover every possibility here, but lets consider some examples based on a company who's financial year runs from April to March:

As you can see, having dates as numbers means we can use EasyMorph functions and simple math to achieve just about any format to describe just about any financial periods we need. And hopefully, you can also take these examples and adapt them to your needs if your company uses different accounting periods.

Handling different timezones

There are EasyMorph users in more than 60 countries around the globe - that means a lot of timezones! Even within a single country there can be multiple timezones, such as in the USA where there are 4 on the mainland alone. Add to this the fact that many countries and regions observe "daylight saving" during the summer months, and it becomes inevitable that at some point, you'll end up having to convert timestamps between different timezones.

Before we look at how we can convert timezones, one important topic for us to briefly cover is "Coordinated Universal Time", also known as "UTC". UTC is a globally recognized standard for how to record time and define how one timezone differs from another. It draws an imaginary line through the earth at zero degrees longitude (also known as the prime meridian) which happens to run very close to where I live here in the UK. Any timezone east of the prime meridian is therefore a positive amount of time ahead of UTC, and any timezone west of the prime meridian is a negative amount of time behind the prime meridian.

As I write this I'm sat in the UK and it is summer time. Because of daylight saving, we are currently in British Summer Time (BST) which is 1-hour ahead of UTC - or UTC+1. In Toronto in Canada where EasyMorph head office is, they are also observing daylight saving and so are in Eastern Daylight Time (EDT) which is 4-hours behind UTC - or UTC-4.

It is important to remember that a timestamp is a date and time combined, such as "2025-04-17 12:00:00". If you need a recap on how these numbers are formed and how we can add hours, minutes or seconds to a timestamp, I suggest rereading part 1 in this series. The most important thing to note, is that a timestamp stored as a simple number has a limitation in that it has no way of including a timezone. You will need therefore to consider what timezone may be relevant for your source of data. Is the source system converting and storing them all in UTC? Or are they stored in another timezone such as the one set on the server where the system is hosted?

OK, now we've considered the theory, let's get to the doing! If the timezones I want to convert between are consistent then I can of course use the addhours() function to add or subtract the number of hours I need to perform the conversion.

addhours([Order Date UTC], -4)

Because UTC is the main globally recognized standard, EasyMorph has two functions for converting between UTC and local time. Local time being the timezone set on the computer or server EasyMorph is running on.

It is also worth noting that sometimes you may come across dates stored as a string which include information for which timezone they are for. Some commonly seen examples are:

You should be able to use what you've learnt about dates and timestamps to interpret these strings, convert them to numeric values and adjust for timezones as needed.

Different epochs and/or intervals

As we covered earlier in this series, EasyMorph dates follow the most commonly used epoch (December 30th, 1899) and interval (a day) for storing dates. This is the same way of storing dates as the most common systems and file types users load into EasyMorph (e.g. Microsoft SQL Server databases and Excel). Probably the next most common method are Unix Timestamps - also known as simply UnixTime. As you might expect, UnixTime is the method used by the Unix operating system and most of it's derivatives. To be awkward, UnixTime uses a completely different epoch (January 1st, 1970 at UTC) and interval (a second). Note that the epoch is specifically "at UTC" and so all Unix timestamps are UTC.

Because both EasyMorph dates and UnixTime dates are numbers, we could certainly just use some math to convert from one to the other. For those that really want to know the math, see below. All you really need to know however, is that the EasyMorph developers have yet again given us some handy functions to do the conversions for us. The fromunixtime() function takes a UnixTime numeric timestamp and converts it to an EasyMorph timestamp numeric value. And as you can guess, the tounixtime() function does the inverse, taking an EasyMorph timestamp numeric value and converting it to a UnixTime numeric value.

This post would be far too long if I tried to cover the math for converting all date and time formats to and from each other. So instead, know that you will be far from the first person to have had to perform the conversion and reach for your favorite search engine or AI tool and you'll find the simple math needed.

Everything else

This series has covered the basics of dates and times as well as the most common manipulations and calculations using them. It certainly hasn't covered all date and time functions, or even all settings of the functions and actions that we have covered. But hopefully it has given you a foundation which you can continue to build on as you work more with EasyMorph and data in general.

For further reading, I'd strongly suggest taking a look at the "Date/time functions" section of the EasyMorph help functions page where you've find both additional functions as well as great detail and examples of each function and it's use.

Also, check out the EasyMorph help pages for the following actions to see all the options and possibilities for their use:

• Make date/time columns action
• Calendar action
• Convert data type action
• Calculate new column(s) action

And of course, as always, the EasyMorph Community forum is a wealth of knowledge, examples and helpful users from around the globe.