Sometimes, a company operates on a bi-weekly cycle in some ways. You just have to either think about it really hard…or experiment until it’s doing what you want. Obviously, you can use this basic formula for whatever “week” criteria you want. It’s the same idea if you wanted to use the first day of the week, with the week defined as starting on Sunday: 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): Make sense (it’s confusing…until it’s not)? 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. The farther you go into a week, the bigger the WEEKDAY() value is. 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. To do this, you can use the WEEKDAY() function. 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. Depending on what your needs are, one approach or the other will make more sense. This formula actually makes the value the 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. 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). I’m not going to discuss either of those approaches…because my preferred approach is to use the TEXT() function.įor the fully written out weekday (“$A3” is the cell with in it - you would just drag this formula down, or, if you’re using an Excel Table, it would autofill): Or, you can make a separate table that maps a number to each weekday and use VLOOKUP to populate the values. You can use the CHOOSE() function and hard code values. But, what if you actually want the day of the week in plain English? The WEEKDAY() function returns a number - 1, 2, 3, 4, 5, 6, or 7. 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. Make sense?īy adding this column, I can create a pivot table that can easily generate weekly data for whatever metrics are in the spreadsheet. As soon as get to (Sunday), I’m in a new week - a week that ends on. The same holds true for Wednesday (1/16), Thursday (1/17), Friday (1/18), and Saturday (1/19). In the example above, is a Tuesday that falls in a week that ends on Saturday. 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.įor 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: 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. So the question is, how could I "fix" this, so how can I get Excel to give me 19 & 01 for 31-12-2018? Ideally I am looking for a solution that does not involve any VBA.Originally written by Analytics Demystified on January 15, 2013 This is then followed by some further processing, summing data based on year and week number, and the issue is that then also the data of the 31st of December 2018 is taken into account for week 1 of 2018, while the 31st of December is obviously not in week 1 of 2018, but in week 1 of 2019. Year is simply determined by using the YEAR function on the date, and the week number is determined using the ISOWEEKNUM function on the date. I'm trying to get a combination of year and week number in the format of YYWW, so for example 1752, or 1801, based on a column with dates, something like this: Date Year Week Several questions have been asked in the past on Weeknumber and Year in Excel, however, I have a specific question that I couldn't find the answer to.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |