How to turn a date into a month with Google Spreadsheets ?

-

Although daily events like sales or customer interactions happen on a daily basis, it's nice to get a monthly graphical perspective. In order to build those nice graphs and monthly trends, you will need to get the month from your daily dates in a nice automated way. In this article, we are exploring various ways in which you can transform your dates in month and more with Google Sheets.

1. The month() and year() functions
The month() function displays the month number from a given date, while the year() function returns the year. You can see below how this works for months (dates are in dd/mm/yyyy).


2. Combining content with the ampersand (&) character
The formula to get the year is =year(A2). If we want to combine in one single cell the year and the month, we can do this easily with the ampersand character.


Combining year(A2) and month(A2) alone works, but the result is 20121 (which is not very intuitive).  To make our date clearer, we add (&) a space, dash, slash enclosed within quotation marks, eg  & "-" & and the end result is 2012-1



3. Formatting the month with Text()
By default, months 1 to 9 only have one digit instead of 01 or 09. We can change this standard in two ways. With the Text function() you can help you convert a numeric/date value into text and then change the displayed format.

The Text() function only takes takes two parameters : the original value and the instruction for the new format. Formatting a month with two-digit is done with "MM". You can get a view of other formatting options on Excel Functions.net. 



4. Using an IF condition to format the month
The IF condition allows you to define a value if a certain condition is met, and another value if it is not. For our date formatting exercise, we are going to add a 0 for all months with one digit, and leave the month as it is for two-digit months.

=IF(the condition;value_to_display_ if condition is met;value if condition is not met)

  • condition : the month number if less than 10
  • condition is met : add a "0" before the month(value)
  • otherwise : leave the month(value) as it is


The simplified formula ...
=IF(month(A2)<10;"0"&month(A2);month(A2))

and the full formula with the year :
=IF(month(A2)<10;year(A2)&"-0"&month(A2);year(A2)&"-"&month(A2)) 



Admittedly formatting the month is much simpler with the Text() function, but we hope this practical example allowed you to discover the powerful IF() function. In the next post, we will discover various ways in which we can turn a date into a quarter.

Have fun with spreadsheets !











Comments

Post a Comment

Popular posts from this blog

How to calculate the quarter for a given date in Google Sheets

-
Image
Quarterly reporting is important for many businesses. While most spreadsheets can extract the day, date, month or year from a date with dedicated functions, none of them have  a built-in quarter() function .  In this article we will show you how you can easily get the quarter of the year from a date. 1. The month() function The month() function in Google Sheets gives you the month number from a date: if your cell A2 contains Sep-30 2017 or 9/30/2017, month(A2) will show "9". 2. A little math There are four quarters in a year with three months each. Month 1 is in Q1, month 5 is in Q2, month 6 is also in Q2, while month 7 is in Q3. The logic is simple enough that we want to automate this with some math. Here is the trick : if we know the month and divide the month by 3, we are almost there : We can see that March, June and December are in the right quarter number but for other months, the floating numbers are cause for concern. Well, not for long. 3. Rounding nu
Read more

How to sort pivot data in descending order in Google Sheets (Part 1)

-
Image
One of the main benefits of working with spreadsheets is the ability to aggregate data, that means to sum up values, count a number of items, etc. Google Sheets has an easy built-in  Pivot Table.  If you are new to spreadsheets, start there. If you are looking for an even more powerful solution, one that helps you have multiple pivot tables on one sheet OR one that allows you to show in which order you want to sort your numbers (ascending, descending), then the  Query() function  is what you need. 1. Pivot Tables In the sheet above, the world's population is listed by country and column D holds the country's region. In the example below, we want to aggregate the world's populations by region. Click on Data, then Pivot Table report... In the Report Editor, the field "Region" is added to the Rows and the field "Population" (which holds the population count from each country) is added to the Values section. You can see that numbers are not
Read more

How to sort pivot data in descending order in Google Sheets (Part 2)

-
Image
In Part 1 , we saw that Pivot table was the classic spreadsheet way of aggregating and displaying numbers from many rows and columns of data. We also saw that sorting the way we wanted is not always possible. Using the query() function is one way of doing that and it opens up many other possibilities. 2. the Query() function The Query() function can easily fix our sorting problem, but it requires some basic knowledge of SQL, a special language used to extract and order data from databases. Fortunately, the syntax used in Spreadsheets is simplified : The Query() is composed of 3 parameters : =query( source of data ,  the query itself ,  the column headers if required ). Here is an example and its result below : =query(facts!A1:D50,"SELECT D, sum(C) group by D order by sum(C) desc",1) This query gets two columns, Region (column B) and the sum of Population (column C) from the sheet called "facts". It then orders the table by decreasing order of Population
Read more