7 most common Google Sheets date formulas every marketer needs

Posted on Posted in Uncategorised
7 most common Google Sheets date formulas every marketer needs

Date column is present in most of the tables every marketer creates on a daily basis. However not every marketer knows these 7 Google Sheets date formulas that surely will make your work easier! 

In this article we are going to review the formulas with the links to the original articles. 

1. How to change date format in Google Sheets

    If you want to change the default date format to a custom one, there is a TEXT() formula that will help you out!

    =TEXT(value, format_text)

    To clarify, by utilizing the TEXT() formula in Google Sheets, you can effortlessly change the date format to suit your preferences and requirements. Whether you prefer day-month-year, month-day-year, or any other format, the TEXT() function offers unparalleled flexibility for customizing date displays.

    You can read a full article about this formula by this link. 

    2. How to get the first day of the month in Google Sheets?

    The DATE() function in Google Sheets basically constructs a date value based on the specified year, month, and day components. It allows you to create date values dynamically by supplying individual components or referencing cells containing these components:

    =DATE(year, month, day)

    However, you can also use the EOMONTH formula. Basically, it represents the end of the month. But we can transform it to show the first day of the month. 

    =EOMONTH(start_date, -1) +1

    The function EOMONTH(start_date, -1) returns the last day of the month. Then, we add one to get the first day of the month for the date.

    Check out the original article by the link to my blog.

    3. How to show the day of the week as a text in Google Sheets?

    Firstly, to show the day of the week, we need to make up a complex formula consisting of WEEKDAY and SWITCH formulas. 

    It’s pretty easy to do.

    =WEEKDAY(date, [type])

    Certainly, here “date” is the cell reference containing the date for which you want to find the day of the week, and “type” (optional) specifies the numbering system for the days of the week.

    =SWITCH(expression, case1, value1, [case2, value2, …], [default])

    Basically, “expression” represents the value or cell reference to be evaluated, “case1”, “case2”, etc., are the conditions to be tested, “value1”, “value2”, etc., are the values to return if the respective case matches, and “default” (optional) specifies the default value to return if no matches are found.

    So, the ready-to-use formula is going to be:

    =SWITCH(WEEKDAY(A1), 1, “Sunday”, 2, “Monday”, 3, “Tuesday”, 4, “Wednesday”, 5, “Thursday”, 6, “Friday”, 7, “Saturday”) 

    Replace “A1” with the cell reference containing the date for which you want to convert the day of the week to text.

    You can read more about this formula in this article.

    4. How to Get the First Day of Week in Google Sheets?

    Among Google Sheets date formulas there is one that will help you get the first day of any week based on a certain date using MOD and WEEKDAY formulas. Firstly, let’s start with the MOD formula.

    =MOD(dividend, divisor)

    =A1 – MOD(WEEKDAY(A1) – 1, 7)

    “A1” corresponds to the cell containing the date for which you want to find the first day of the week.

    Understanding the Formula Breakdown Let’s break down the components of the formula to understand how it works:

    • WEEKDAY(A1): This function returns the day of the week corresponding to the specified date, with Sunday being represented as 1 and Saturday as 7.
    • MOD(WEEKDAY(A1) – 1, 7): Here, we subtract 1 from the result of the WEEKDAY() function to align Sunday with 0 and Saturday with 6. We then use the MOD() function to ensure that the result falls within the range of 0 to 6.
    • A1 – MOD(WEEKDAY(A1) – 1, 7): Finally, we subtract the result obtained from the MOD() function from the original date (A1) to find the first day of the week.

    Full article about this function can be found in my blog. 

    5. How to Add Months to Date in Google Sheets? EDATE () formula

    The EDATE formula, a function within Google Sheets, enables users to calculate dates by adding or subtracting a specified number of months from a given date. 

    =EDATE(start_date, months)

    • start_date: This parameter denotes the initial date from which the calculation begins.
    • months: Indicates the number of months to add or subtract. Positive values add months, while negative values subtract.

    Check out the full article about this formula. 

    6. How to calculate differences between dates in Google Sheets?

    Basically, DATEDIF function is a pretty simple function, specially designed to determine the difference between two dates in Google Sheets in various units, including days, months, and years. Its syntax is straightforward:

    =DATEDIF(start_date, end_date, unit)

    • start_date: This is the initial date in your calculation.
    • end_date: This is the final date in your calculation.
    • unit: Specifies the unit of measurement for the difference (“D” for days, “M” for months, “Y” for years, “MD” for days excluding years and months, and more).

    If you are willing to read the full article, you can do it using the link. 

    7. Get the month name from the date in Google Sheets

    If you want to extract month from the day, you cannot miss this very easy formula:

    =TEXT(A2, “M”)

    “A2” corresponds to the cell containing the date, and “M” means month, in other words, part of the date we want to extract.

    In result, you will see the number of the month. That’s not the thing we want.

    Hence:

    • If you type “M” – the result will be a simple number of the month, for example, 3
    • If you type “MM” – the result will be 03
    • If you type “MMM” – the result will be Mar, as a shortened month name
    • If you type “MMMM” – the result will be March – full month name. 

    Here is the link for the full article.

    You can use these tricks to use in your Google Sheets while working with marketing reports. Moreover, In Gaille Reports we sell automated report templates, you can check them out in our template store.

    Summary

    Summing up, in this article we’ve reviewed 7 most common Google Sheets and most useful date formulas Google Sheets users need to know. In each of the sections you can find direct links to our articles, or you can find them in our blog. In addition, there are YouTube Shorts tutorials about these formulas that you can find in Gaille Reports channel.

    Hope you liked this article! Was it useful? Please, share your opinion with us! Subscribe to our newsletter to be updated with new articles every month!