# Converting a Date in Excel to Week, Bi-Week, Month, and More

**Tim Wilson**on

**January 15, 2013**

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:

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.

### 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

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!

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

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

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:

=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!

Julien CoquetJanuary 18th, 2013Hi Tim,

unless you’re stuck with Excel 2003 which sucks at time/date support, look into the EOMONTH() funtion to directly compute the last day of month 😉 (works with Excel 2007 and later)

http://office.microsoft.com/en-001/excel-help/eomonth-HP005209076.aspx

Julien

Tim WilsonJanuary 21st, 2013Author's ReplyThanks, Julien! EOMONTH() is officially in my repertoire, now! It looks like it requires the Analysis ToolPak be installed to work. Any self-respecting analyst is going to have that enabled, but it would affect the portability of spreadsheet a bit, wouldn’t it? Very handy!

password recoveryJune 27th, 2013Hi! I know this is kind of off topic but I was wondering if you knew

where I could find a captcha plugin for my comment

form? I’m using the same blog platform as yours and I’m having problems finding one?

Thanks a lot!

Prashanth RajuMarch 7th, 2014This is wonderful piece of information that helped me in creating my end of week metrics of the daily data my system was logging. Thank you friend.

Trudy SmithMarch 13th, 2014This is really useful. Thanks!

One question – how do I create Thursday – Wednesday weeks & bi-weeks?

Tim WilsonMarch 14th, 2014Author's ReplyHi Trudy,

To do Thursday – Wednesday where the start of the week (Thursday) is the date used: =$A3-WEEKDAY($A3-4)+1

To do this bi-weekly, depending on which week you want to be the first week, it would be either:

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

or

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

Does that work?

Trudy SmithMarch 17th, 2014Thank you! worked perfectly!!!!

RhysApril 10th, 2014I work on manufacturing month ends for weeks pattern 4-4-5. So in March the month end is the 5th April. I then have a list of invoice dates and want to return what financial month it is in, for example invoice date 4th April would return March, but one on the 6th April would return April. Help?

Tim WilsonApril 10th, 2014Author's ReplyThat’s a good one. Retail calendars tend to give me fits as well, as I don’t think Excel has any sort of native awareness there.

The brute force method would be a hidden tab (or xlVeryHidden tab) with a table that lists all dates and their respective financial month, and then use a VLOOKUP to get that information for each invoice.

You could also make that a 12-row table rather than a 365-row table by listing the *start* day for each fiscal month and the fiscal month that it applies to. Then, do a VLOOKUP from your invoice day without using the last optional parameter (or set it to TRUE — but that’s the default). Even though it won’t find an exact match, it should find the right month.

Make sense?

RhysApril 11th, 2014I was trying to avoid vlookups. In the end opted for converting the date to a number and then “=IF(AND([@[Numerical Date]]41731),”Mar”,[@Month])” then i thought when the problem arises again in June ad more criteria to the formula.

Tim WilsonApril 11th, 2014Author's ReplyThat makes sense. As you roll from year to year, though, you’ll need to update, right (which you’d need to do with the VLOOKUP approach, too — adding rows to the table). I wonder if you could figure out the numerical date of the first day of the year of the invoice and then have a series of nested IFs from that point that break out the monthly buckets? It would be a messy formula.

Roy BurtonOctober 1st, 2014how would I create a simple spreadsheet for a full 12 month period with 31 days, where every month you must tabulate the number of interviews completed on a daily basis.

Tim WilsonOctober 4th, 2014Author's ReplyI’m not sure I understand the question. Can you provide more detail?

Dr. BuchananOctober 26th, 2014I have a column vector of weekly dates that match weekly price changes and two additional column vectors of daily dates and prices that I want to contemporaneously match to the first column, deleting the daily observations that lie between. I have been painstakingly plodding through the daily column vectors (deleting and moving up the date and corresponding price to match the first column). There has to be an easier way? Can you recommend something? Thanks!

Tim WilsonOctober 27th, 2014Author's Reply(I followed up with Dr. Buchanan offline, and the solution here was a VLOOKUP — not strictly germaine to the main content of this post, so not posting details here.)

litisa senapatiSeptember 10th, 2015I need one help….I need to calculate the effort based on a weekly ramp up plan into a monthly estimate for which I need how many days per week in a particular month formula….for eg. my week 1 starts on 3rd Aug 2015….Aug will have 4 complete weeks and 1 day of 5th week; how can I automate this by a formula?

Tim WilsonSeptember 12th, 2015Author's ReplyI’m not quite following what you’re trying to do. August 3, 2015 is a Monday, so I assume your week runs Monday to Sunday? But, at the end of the month, you’re saying August 31 is a Monday, so that’s the “4 complete weeks plus 1 day?” This sort of sounds like a retail calendar of some sort on the front end, where it runs in complete weeks, but, then the end of the month is a calendar date, which does *not* sound like any version of a retail calendar. Can you provide more details on what defines the start and end of each month?

Kelly GoOctober 13th, 2015need date to be converted into month week (e.i 9/2/15 = September Week 1), any possible formula for this?

Tim WilsonNovember 10th, 2015Author's ReplyAnything is possible! What logic are you looking for when weeks split a month? Would “September Week 1” be 9/1 through 9/5/2015? Would 9/27-9/30/2015 be “September Week 5?” In other words, would the first and last weeks be partial weeks?

emmanuel moutonNovember 23rd, 2015great spreadsheet you made, works fine, big time saver. thanks

Tim WilsonDecember 4th, 2015Author's ReplyThat’s great to hear!

Scott NeelsFebruary 20th, 2017Thanks for this. I really appreciate it.

Erin TaylorFebruary 22nd, 2017Hi, thank you for this! I need to identify each date and categorize into weeks (each week being a number starting at one. My start date is the beginning of our fiscal year 7/1/2016. I can either number the weeks in chronological order or list the week from start date to stop date. My weeks are week days only, Mon-Fri. I would love some help!

Tim WilsonFebruary 28th, 2017Author's ReplyI’m not sure I’m quite following what you’re looking to do. Are you trying to identify which week of the fiscal year each date is, or are you trying to actually generate the date ranges for each week? I’m also a bit confused by the fact that your fiscal year started on 7/1/2016 — which was a Friday — but you only need weekdays data. Does that mean that the first week of the fiscal year was only a single day? I’m sure there is a fairly straightforward way to do this — I just need a better understanding of exactly what you’re trying to do. Maybe an example would help?

JonathanApril 6th, 2017I don’t know if this is too old of a post to get an answer, but I have a similar problem I have not been able to solve.

I work for a retail company, and receive a daily excel report including all SKUs on order and their ‘ship date’, which can span any day of the month. What I need to do is generalize these ship dates into “delivery months”, or what month we will actually receipt the goods at our fulfillment center. So for example, any SKU with a ship date falling between 1/15/17 and 2/15/17 would have a delivery month of 2/17. Currently I am doing this manually, but I’d love to just pop a formula in. Any guidance you can provide is appreciated!

Tim WilsonApril 12th, 2017Author's ReplyIt’s not the age of the post, but the schedule of my travel. 🙂

Is the 15th of the month always the cutoff? In other words, anything where the ship date is before or on the 15th will have a delivery date of the current month, and anything that has a ship date of the 16th or late will have a delivery date of the following month? If that’s the case, the variable number of days in different months makes me think an IF statement is probably the way to go. If the ship date is in cell A1, then:

=IF(DAY(A1)<=15,DATE(YEAR(A1),MONTH(A1),1),DATE(YEAR(A1),MONTH(A1)+1,1))

What that will return is a date that is the first day of the current month (if "DAY(A1)15″).

Does that do it?

AngelitaMay 15th, 2017I’m not entirely sure if what I’m looking to create is even possible as a formula, I may have to stick to manual inputs but we all know if we don’t ask, we’ll never know!

My company is going through many changes currently and I’m trying to create a report for various people that are freaking out due to change, they receive vendor invoices weekly, bi-weekly, monthly, quarterly and annually to ensure they know when to expect the invoices as well as when/if they’re missing one that never came in.

Is there a formula for inputting the current month and having it load the dates into selected columns? (Example: May 2017) If I enter 5/1/17 in a field, can it populate the weekday weeks into fields (May 1-5, 8-12, 15-19, 22-26, 29-02) ?

Thank you in advance for your time! (I apologize if this is not a post to ask this on, I’m just stumped with this 🙁 )

-Angelita.

Tim WilsonMay 16th, 2017Author's ReplyThat definitely is doable! The tricky thing is figuring out what the first week of the month should be. For May 2017, the 1st of the month is a Monday, so it’s kind of easy. But, 6/1/2017 is a Thursday. What is the first week there?

Or, April 2017 was an interesting one, because the first of April was on a Saturday.

Below is a screen cap showing a couple of different options. The top image is the “results,” while the bottom image are the actual formulas. It’s shown with intermediate cells so that it’s easier to follow the logic. But, the formulas could be strung together so that just column D (or column H) gets used. It will take a little adjusting, I’m sure, to get to exactly what you want, but, hopefully, this is a good starting point.

https://uploads.disquscdn.com/images/536d3accbbac247faa495d0bbbf840a3fa1330f641854d76bbe54dca9118c07f.png

AngelitaMay 16th, 2017Thank you so much!!!!

veb2May 23rd, 2017In an Excel Dashboard, would you set this up in the transformation layer normally?

Tim WilsonMay 23rd, 2017Author's ReplyOften, I find I have to put this on the raw data layer. The reason is that, if I, say, have raw data that is daily, but I want to show the data weekly, then I’ll have one of these formulas butting up to the table of raw data calculating the “week.” Then, a pivot table based on that date is what goes in the transformation layer. Or, to extend that a bit, I’ve had cases where the reason I have daily data is because I want to be able to present the data on the presentation layer as weekly *or* monthly based on a dropdown. So, I’ll use an IF() statement with two of the formulas in this post to dynamically calculate a “rollup date” that is either the first day of the month or the last (or first) day of the week. That way, it’s just one pivot table that uses that rollup date as the row value.

veb2May 24th, 2017Thanks! This site is probably the most helpful resource I’ve come across in a long time.

A little off topic for this page, but when using pivot tables in that way, how do you get it to auto-refresh when the “rollup date” changes? I prefer to avoid macros in my dashboards if I can, but maybe you use them?

Tim WilsonMay 24th, 2017Author's ReplyThat’s a good question. I also avoid macros if I can. That’s certainly one option — it’s a small script that would run whenever that cell changes. But, various things I’ve done:

– Set the pivot table(s) to refresh on file open. That doesn’t address changing the dropdown, but it does work well if there is other data automatically being populated in the file

– Put a note next to the dropdown that says, “Select Data >> Refresh All after changing this dropdown.”

– Put a little trickery in a formula that can detect if the dropdown doesn’t synch up with the pivot tables (for dates, for instance, just have a check of two cells in one of the pivot tables to see if they are 7 days apart. If they are, and the dropdown is set to “Monthly,” then display the “Select Data >> Refresh All” message. Otherwise, just display “” (nothing). That way, the message only shows up when it’s needed. (Obviously, the check has to also do the reverse — check that it’s not set to weekly when the distance between those two cells is *not* 7 days.)

Michael BartonJune 7th, 2017Hi, hope you can help with this one.. I’m forecasting when Benefit monies will be received according to payment frequency whether its weekly, bi-weekly, 4 weekly or monthly. I use a validation list so a user can choose which payment frequency applies to a benefit. And the user puts in a start date for each benefit. I’m trying to use an IF formula to forecast the dates on which payments will come in. I can get a standalone formula to work for monthly, or a standalone formual for any combination of weekly payments. But I cant get an IF formula to work that will recognise “Monthly” or “2 weekly”, and then apply the right criteria. I keep getting a FALSE result.

Not sure whether that makes sense. If it does – do you have any suggestions ?

Thanks

Tim WilsonJune 7th, 2017Author's ReplyThe fact that you’re getting FALSE rather than an error sounds like the issue is that, in your nested IF’s, none of the criteria are being met, and there isn’t an “else” clause (something after a final comma before a bunch of closing parentheses). So, simply “FALSE” is being returned. I’d start with the cursor after the last parentheses, and then work your way back one by one to make sure it’s closing each IF() statement as you’d expect. Then, I’d check that you do have something specified if NONE of the criteria are met (I like to put something like “NONE MET” as a final clause. That’s a way to check that the issue is, indeed, that none of the IFs are being met. It should definitely be doable, though, as you’ve described it.

a guy's mindAugust 3rd, 2017I have a situation where employees are supposed to enter the weekending date on their expense reports they submit, but they are constantly entering in other dates. Is there a way to calculate the weekending (Saturday) date and have it replace whatever date was entered by the employee (based on what they entered). I’m thinking it might need to be a macro of some sort, but I can’t work it out how I would do this exactly. Any suggestion?

Tim WilsonAugust 3rd, 2017Author's ReplyA macro could do it but… macros! Blech! If it’s possible to change the structure a little bit to have a hidden column that has the “right date” — maybe even have that column right next to the date they enter. That column could calculate the “right” date: =A1-WEEKDAY(A1)+7 will return the Saturday at the end of the week for whatever is in A1.

So, then, you could have any formulas/calculations that count on the date being a Saturday to use that hidden column.

And, you could even hide the “user-entered” column and unhide the “calculated” column to get a view that is the proper dates.

Assuming they’re entering the expenses in Excel, you could also use Data Validation to guide them to enter a Saturday date. A “Custom” data validation criteria of =WEEKDAY(A1)=7 with a Error Alert that tells them they need to enter a Saturday date would prevent them from entering anything else. Alternatively, you could use conditional formatting to turn the cell red using the same logic if they don’t enter a Saturday date.

a guy's mindAugust 4th, 2017Thanks. That’s exactly what I needed; Some ideas that didn’t use a Macro. I also prefer not going that route.

Have a great day!

Crayton HeinrichsAugust 17th, 2017I am working on a database and want to put back to back same dates in column A. Example

Monday, August 21, 2017

Monday, August 21, 2017

For the entire year. What is the best way to accomplish this?

Tim WilsonAugust 17th, 2017Author's ReplyI’m not sure I understand exactly what you’re trying to do. If you have a date in cell A1 and want that repeated for all other cells in column A, you could make A2 be a formula of either “=A$1” or “=A1” and then just drag that formula down to autofill the remaining cells.

Karla MartinAugust 30th, 2017I am working on building a timesheet for payroll. Employees get paid on the 15th and the last day of the month. I would like them to put in the start date of the pay period (ex: 2/1/17) and let it fill in each date of the pay period. I am having issues building a function that knows to leave it blank after the 15th or blank after the last day of the month.

Please see highlighted example. Can you help? https://uploads.disquscdn.com/images/e22952bcbb312575779696efd7aaa8bf3c0846a38b3737d6e779e607accd9d04.jpg

Tim WilsonAugust 30th, 2017Author's ReplyI think the easiest thing to do is to put an initial IF in that checks if the previous day is blank and then retain that blankness if it is.

I couldn’t get your exact formula to work (check as I might, there seem to be extraneous close parentheses), but the below is the same idea and seems to do the trick:

=IF(A6=””,””,IF(DAY(A6)=16,MONTH(A6+1)=MONTH($A$1)),A6+1,””)))

For the approach above, the logic is:

1. Check if the cell to the left is blank. If so… then make the current cell blank

2. Check if it’s a “first half” date. If so…then increment the date.

3. Check if it’s a “second half” date AND that the next date wouldn’t roll into a new month. If so…then increment the date.

4. Leave the cell blank. We’ve rolled over to a new period.

Alternatively, steps 2 and 3 could be collapsed into an OR(X, AND(Y,Z)) format, which would reduce one of the IF statements. Overall — shortens the formula a bit, but makes it a bit tougher to read:

=IF(A6=””,””,IF(OR(DAY(A6)=16,MONTH(A6+1)=MONTH($A$1))),A6+1,””))

See if that works?

Karla MartinAugust 30th, 2017I had trouble at first as the email I received added some letters to the formula but after I figured that out, it worked perfectly! Thank you so much for your time and effort! 🙂

Jennifer Suzanne BorthwickSeptember 13th, 2017I want to take data from one worksheet and put it on another worksheet. Here is the formula I have for the first cell: =GETPIVOTDATA(“Sum of Invoice Total Minus Tax”,’Daily by BC Date’!$A$3,”Order Date”,DATE(2017,7,4)). Now, how do I increment the formula so that the date increases by one day and returns the data for the next day?

Tim WilsonSeptember 13th, 2017Author's ReplyI run into this type of use case pretty often, if I understand correctly. Presumably, on the worksheet where you’re putting the GETPIVOTDATA() formula, you also show the data that you’re looking to get data for? If so, then just change “DATE(2017,7,4)” to be a reference to that cell. What I often have is a sheet where I have a series of dates — such as all of the days for a month — running down one column. Then, a GETPIVOTDATA() formula in another column that references that initial column — with a cell reference, I can just drag it down and the GETPIVOTDATA() formula will pull the correct data for each row.

Does that get at what you’re looking to do?

Jennifer Suzanne BorthwickSeptember 13th, 2017Yes, it does! I actually used a VLOOKUP instead, and that seemed to work fine; but I like knowing both methods! Thank you for such a quick response.

Fahim IdhaNovember 4th, 2017I do have a column X “Due Month” and another “Task completed” .I want to do a vlookup whereby if the referenced cell in task completed column is “yes” then then vlookup should increment the month +1. Is that possible

Tim WilsonNovember 4th, 2017Author's ReplyIt’s a little unclear as to the exact structure of your data, but I suspect the answer is yes. Let’s say the base month (that will need to be conditionally incremented) is in cell A1. Then:

=IF([logic to check whether to increment], DATE(YEAR(A1), MONTH(A1) + 1, 1), A1)

That would increment the value in A1 by a month (and setting it to the first day of the month) if the condition is true (task completed = “yes,” presumably), but set the value to be the same as A1 otherwise.

Fahim IdhaNovember 4th, 2017That’s it. It worked . Thanks a lot.

Tim WilsonNovember 5th, 2017Author's ReplyGreat!

Filip ŁaweckiNovember 27th, 2017My company is using a date format for weekly reporting that looks like this: W1,W2,W3,W4….

Is it possible to convert them to months?

Tim WilsonNovember 27th, 2017Author's Reply(Almost) anything is possible. The devil is in the details, though, since, presumably, one week can span multiple months. Can you provide some examples of the exact “WX” (for instance, is it just “W1” or is it “W1-17” or “W1-2017” or something else) and which months specific weeks would match to? For instance, rolling into 2017, would “W1” be “31-Dec-2017 through 06-Jan-2018” and, if so, should that be “January 2018” (the month the week ends in), “December 2017” (the month the week starts in) or something else? Or, would “W1” be a “partial week” that only runs from 01-Jan-2018 to 06-Jan-2018 (in which case, presumably, it would be “January 2018″… but then the question arises for the week spanning from January to February 2018).

Filip ŁaweckiNovember 30th, 2017Sorry for not being specific enough. The week format is just as I described and consists of a letter W with a number of the week, e.g. W1, W45, W18… The general rule is that if some weeks starts another month, say, in Friday there is a beginning of January, this week will still belong to the previous month, December. With this given, I want to make a formula that will change the number of the week to an assigned month.

Thank You Tim 🙂

Tim WilsonNovember 30th, 2017Author's ReplyGot it. I’m sure there are more elegant approaches. But, the two images below show both the “end result” (showing for 2017, 2018, and 2019), and then the formulas used to get there.

The formulas in the second could be collapsed into a single cell for each week, but, for explaining the “how,” I split it up a bit. The explanation:

B3

This figures out what day of the week the first day of the year is. 2017 was atypical, in that the first of the year was actually the first of the week (Sunday).

=WEEKDAY(DATE(B2,1,1))

B4

This figures out what the start of the week for W1 is for the year. It uses the value in B3. The “IF()” is because the years (like 2017) where the first day of the week is a Sunday, the rest of the formula actually doesn’t quite work. The “meat” is the “DATE(B2,1,9-B3).” It’s basically saying, “Find the first Sunday in the current year.” I suspect there is a more elegant way to do this.

=IF(B3=1,DATE(B2,1,1),DATE(B2,1,9-B3))

B6 (Which can then be dragged down to all the other rows)

This does four things:

1. It strips off the “W” to get the actual week number (that’s the RIGHT() part of the formula)

2. It subtracts one from that and then multiplies it by 7 to get “how many days from the first ‘day’ of the year

3. It adds that to the value in B4 to get the start date for that week

4. It converts that date to be the first day of the month (a step that could actually be skipped — depends if you want all of the weeks for the month to actually HAVE the same ‘month’ value or if you just want the cells do display as the month value).

The cells are then formatted to display Mmm-YY format.

=DATE(B$2,MONTH(B$4+(7*(RIGHT($A6,LEN($A6)-1)-1))),1)

So… it’s messy, but it’s doable.

https://uploads.disquscdn.com/images/0502099abfe9874386da2525db8379490760453bb9f62be7f8fa01460946581d.png

Tyra NuarDecember 21st, 2017So I am working on a gantt sheet that I created. However I need to change the daily to a weekly or monthly calculation. Is there a way to do this.

(This is the format that I have going across the top.) It goes from Mid December through the end of January. Any help would be amazing at this point.

Tyra NuarDecember 21st, 2017https://uploads.disquscdn.com/images/fedc33f2394b940903a64b2db86de5af4b9af190d4f3fffd8aa22b8c04d61a12.jpg

Tim WilsonDecember 21st, 2017Author's ReplyAre you hand-coding in the Gantt chart which cells are “filled,” or is that pulling from an underlying table of data that records start / stop dates? It’s easy enough to convert any date to be the week (either start of the week or end of the week) or month (same thing — first day of the mont or last day of the month). That’s the core content in this post, though. So, it sounds like you’re looking to do something in addition to that, and I’m not sure exactly what that is.

Tyra NuarDecember 21st, 2017I hand coded the cells which are filled. Using the (;;;) in formatting. I have my dates linked to the filled cells via a formula =IF(AND(G$1>=$B2,G$1<=$C2),$E2,""), I have tried to change the dates via the information above with no success. I may have to change the dates and then insert the formula again. Any other suggestions.

Tim WilsonDecember 21st, 2017Author's ReplyAh. Okay. I wasn’t quite mentally picturing the spreadsheet structure. Column B has the start date and column C has the end date. Let’s tackle weekly. The wrinkle is that, presumably, if the start date falls anywhere in the week, then you want the cell to fill. If the end date falls anywhere in the week, then you want it to fill as well. (And, every cell in between.)

Let’s assume you’re going to set your dates in row 1 as the Sunday of the week (I’d set the first one and then make every cell to the right be a “+7”).

In that case, if you just update your formula in G2 to be =IF(AND(G$1+6>=$B2,G$1<=$C2),$E2,""), I think it will work.

A similar approach could be taken for monthly.

Does that do the trick?

sungjaemelodyDecember 27th, 2017Hi, I think I may be out of this topic but I want to ask is it possible if I want to get the data from google into excel ? Then, is it possible if I want to it update automatically ?

I will give image for u better understand

sungjaemelodyDecember 27th, 2017For example I search keyword effector on google & the first page & 3rd line of search result is about my company, i will fill in as d&s store searched. But, if i search cleansing water & the search engine shows about my rival, i will fill in as high/medium/low based on how many rival on d&s unsearched. third, If i search aloe vera & there is no berkait with my company or my rival in the search engine, i will tick it as a no result.

so, my question is, can i get these data from google into excel & update it automatically, daily update? is there any apps or any solution? or is it imposible?

https://uploads.disquscdn.com/images/cf8758a0823de775e98844291ccb4729dcd5b7223520441cea6cc1fbe46be0eb.png

sungjaemelodyDecember 27th, 2017*there is no related with my company

Tim WilsonDecember 27th, 2017Author's ReplyThat’s quite a bit off topic for this post, but using the keywords “SERP,” “SEO,” and “Excel” should turn up quite a few posts (as one would expect — good SEOs should have blog posts that bubble up in search results). This post is five years old, but looks to have a pretty good list (if it’s on Moz, it’s likely pretty solid): https://moz.com/blog/excel-and-google-docs-tools-for-the-ultimate-seo-dashboard.

Alex AmpiawMarch 13th, 2018I want to create an excel bi-monthly date grouping based on our payroll cycle, and it works fine with the auto-grouping in Pivot tables to a point. The challenge has to do with the uneven number of days in different months. For instance, February has only 28 days, so the date groups begin to cross months after a while. Is there a way around this in excel?

KitJune 21st, 2018Can we convert to BI-MONTHLY DATE and add one more criteria to return the month;

if the date is 1st half of the month 1- 15, then return the previous month

if the date is 2nd half of the month 16-31 , then return the current month.

e.g.

7/15/2018 -> 7 (July)

7/31/2018 -> 7 (July)

7/14/2018 -> 6 (June)

7/1/2018 -> 6 (June)

https://vanphongchothuesaigon.net/September 28th, 2018Can I simply just say what a relief to discover a person that actually understands what they are discussing

on the net. You certainly understand how to bring a problem to light

and make it important. More and more people have to look at this and understand

this side of your story. I was surprised you are not

more popular given that you surely possess the gift.

S.meyerDecember 20th, 2018I work with payroll in accounting. Our company brings bootworld to our work and we are able to pay for our shoes or whatever and have it deducted from pay check.

We can pay over the course of 6 pay checks… so in a spread sheet i need to figure out to divide the total amount by 6 and have my 6 payments equal the total but as I do, it does the same amount for each column, whih does not equal the total…usually its off by a few dollar or just cents.. how do i fix this, is there a formula i can use.