Convert Dates In Excel

To ungroup dates in a Pivot Table, simply right-click on the dates column and select ungroup. Or, you can go to the PivotTable Analyze tab and select Ungroup. Once this is done, the data will be ungrouped again. Control Automatic Grouping. If you wish to, you can easily turn off this automatic date grouping feature in Excel 2016.

  • Excel Dates Displayed in Different Languages. We use the TEXT Function to convert the dates by specifying the language ID in the format argument of the formula. For example: =TEXT('1/1/2017',' $-0809 dddd') =Sunday. Where $-0809 is the language ID for English, and dddd tells Excel to covert the date to the full name of the day.
  • One common thing many people need to do often in Excel is to remove time from dates (i.e., remove the time from the time stamp so that you only have the date). Below is an example where column A has the timestamps (with date and time both) and Column B only has the date value.

I often find myself getting data out of one system or another (or multiple systems, and then combining them) as “daily” data — a series of metrics by day for a sequence of days. Sometimes, I work with that data at a daily level, but, often, I want to roll the data up by week, by month, or by some other time period.

For instance, if I want to look at the data weekly, I’ll use either the last day of the week or the first day of the week and then use a formula in a new column to convert each actual day to the “week” in which it falls:

Dates

In the example above, 1/15/2013 is a Tuesday that falls in a week that ends on Saturday, 1/19/2013. The same holds true for Wednesday (1/16), Thursday (1/17), Friday (1/18), and Saturday (1/19). As soon as get to 1/20/2013 (Sunday), I’m in a new week — a week that ends on 1/26/2013. Make sense?

By adding this column, I can create a pivot table that can easily generate weekly data for whatever metrics are in the spreadsheet.

This approach works for a number of different ways you might need to roll up daily data, so I thought a post that walks through some of the more common ones and the formula to carry out each conversion was in order. I’ve put all of the examples in this post in a downloadable spreadsheet that you can check out and play around with.

Convert Dates In Excel From Uk To Us

Day of Week

The WEEKDAY() function returns a number — 1, 2, 3, 4, 5, 6, or 7. But, what if you actually want the day of the week in plain English?

You can use the CHOOSE() function and hard code values. Or, you can make a separate table that maps a number to each weekday and use VLOOKUP to populate the values. I’m not going to discuss either of those approaches…because my preferred approach is to use the TEXT() function.

For the fully written out weekday (“$A3” is the cell with 1/15/2013 in it — you would just drag this formula down, or, if you’re using an Excel Table, it would autofill):

=TEXT($A3,”dddd”)

For the 3-letter weekday:

=TEXT($A3,”ddd”)

Easy-peasy, no?

Note: If you simply want the date to be displayed as the weekday, you don’t need a formula at all — you can simply change the cell formatting to a custom format of “dddd” (for the full weekday) or “ddd” (for the 3-letter weekday). If you do that, the display of the data will be as a weekday, but the underlying value will still be the actual date. This formula actually makes the value the weekday. Depending on what your needs are, one approach or the other will make more sense.

Convert to “Week Of”

The example I started this post with is converting each day to be the day that ends the week or the day that starts the week. To do this, you can use the WEEKDAY() function. The easiest way to understand how this works out is to write out (or put in Excel) a series of dates and then write the numbers 1 through 7 as you go down the dates. The farther you go into a week, the bigger the WEEKDAY() value is. So, if you subtract the WEEKDAY() value from the actual date, you will get the same value 7 days in a row, at which point the value will “jump” seven days. Make sense (it’s confusing…until it’s not)?

So, to convert a date to be the Saturday of the week the date falls in, use this formula (the “+7” just keeps the converted value from being the Saturday of the previous week):

=$A3-WEEKDAY($A3)+7

It’s the same idea if you wanted to use the first day of the week, with the week defined as starting on Sunday:

=$A3-WEEKDAY($A3)+1

Obviously, you can use this basic formula for whatever “week” criteria you want. You just have to either think about it really hard…or experiment until it’s doing what you want.

Convert to Bi-Weekly Date

Convert Date In Excel To Yyyymmdd

Sometimes, a company operates on a bi-weekly cycle in some ways. For instance, a lot of companies pay their employees every two weeks. WEEKDAY() doesn’t work for this, because it doesn’t tell you which of the two weeks a day falls into.

In this case, I use MOD(). This function is, basically, a “remainder” function, and its main use is to calculate the remainder when one number is divided by another (for instance, “=MOD(14,4)” returns “2” because, when you divide 14 by 4, you get a remainder of 2).

Well, Excel dates are, under the hood, just numbers. You don’t really need to know exactly what number a date is (although you can change the cell formatting to “Number” when a date is displayed and you will see the number). But, if you think about it, if you divide a date by 14, it’s going to have a remainder between 1 and 13. Let’s say the remainder is “2.” So, what will the remainder be if you divide the next day by 14? It will be “3.” And so on until you get to 13, at which point the next day, if divided by 14, will have a remainder of 0. Hmmm. This seems like we’ve recreated the WEEKDAY() function used above…but with a 14-day long period instead of 7-day one, right? Exactly!

Convert dates in excel to calendar

So, if we wanted to convert a date to be the Saturday at the end of the bi-week period, it would be one of these two formulas (depending on which Saturday is the cutoff and which is the mid-period point):

=$A3-MOD($A3-1,14)+13

Excel

or

=$A3-MOD($A3-8,14)+13

If you wanted to use the start of the period, with the week starting on Sunday, then it would be one of these two formulas:

=$A3-MOD($A3-1,14)

or

=$A3-MOD($A3-8,14)

Again, it takes some experimentation if you want to adjust to other dates, but the “14” will not change as long as you’re working on bi-weekly periods.

Convert to Bi-Monthly Date

Sometimes (again, company pay periods are a good example), rather than using a bi-weekly calendar, you want to use a bi-monthly calendar — every date from the 1st through the 14th should be converted to the first day of the month, and every day from the 15th through the end of the month should be coded as the 15th. To do this, we use the DATE() function with an IF() statement for the day value:

=DATE(YEAR($A3),MONTH($A3),IF(DAY($A3)<15,1,15))

We know the year is the YEAR() of the date being converted, and we know the month is the MONTH() of the date being converted. But, we need to look at the day of the month and check if it is less than 15. If it is, then we return a day value of “1,” and, otherwise, we return a day value of “15.”

Convert to Monthly Date

Convert Dates In Excel To Month

Monthly is almost as common, if not moreso, than weekly. To convert to the first day of the month is a straightforward use of the DATE() function. We pull the year using the YEAR() function on the date we’re converting, the month using the MONTH() function on the date we’re converting, and then simply hard code the “day” as “1:”

=DATE(YEAR($A3),MONTH($A3),1)

But, what if we want to use the last day of the month? We can’t hard code the “day” value because that day could be 28, 29 (leap year), 30, or 31. Curse you, Gregorian calendar!!!

Well, actually, this isn’t all that complicated, either. Why? Because the last day of the month is always the day before the first day of the next month. Huh? That’s right. That’s how we get the last day of the month: we use the DATE() function to figure out the first day of the next month (by adding 1 to the MONTH() value)… and then subtract 1:

Convert Dates In Excel To Text

=DATE(YEAR($A3),MONTH($A3)+1,1)-1

How do you like them apples?! [idiom ref.]

As an interesting aside, it would be understandable if this formula broke for the month of December. In that case, you’re actually telling Excel to calculate a date where the month is “13.” Luckily, Excel figures out what you mean and winds up returning January 1 of the following year (from which the formula then subtracts one to return December 31st).

But, What About…

I’ve just scratched the surface with possible date conversions in this post. Hopefully, the different approaches I described will trigger an idea or two for your specific situation. But, if you’ve got one that is stumping you, leave a comment here and I’ll take a crack at it!

And, all of the examples here are included in this downloadable spreadsheet. You can change the “start date” in cell A3 and all of the subsequent dates will automatically update. Happy date converting!