A great question from one of my Excel seminars started out as a simple question: how can I convert a column of dates to show if the day is Monday, Tuesday, etc.? However, upon further examination, they were trying to figure out the banking day before the date shown. This could have been an ugly combination of IF functions to locate Mondays and Bank Holidays. However, one obscure function solves this problem in a short formula. Episode 644 shows you how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast. I'm bill Jelen.
Today we have a question that came in one of my excel seminars.
It was a question from somebody who works at a bank and they said, Hey we're always dealing with bank holidays and with basically figuring out what day of the week something fell on and I said, okay.
Well, you know you don't have to do any formula at all.
First of all if you have a series of dates, you can choose that series of dates and go into "Format" "Cells" and of course there's a whole bunch of date functions here.
None of which are exactly what you want.
But if we go into custom and basically what happens is when you put in a single d or dd, you're just going to get the day of the month.
So for example, here February 23rd is going to give me a 23 but if I put in ddd, it's going to put Fri and dddd will give me Friday. So let's just format that column and basically say, Hey use the 4D format.
dddd and it will show me the day of the week.
Now if you need to do this with a formula.
We can say that we want to use the text =TEXT of cell A3 and what format we want to use.
Of course it's the "dddd" Close that quote, close the parentheses,copy down and we get the exact same value.
So there, there's a formula that will calculate that Now I had to point out that if you need to sort it by day of the week interesting tool "Data", "Sort" Sort by "Day of the week" but then say "options" and then instead of using a normal sort order we want to sort it into "Sunday Monday Tuesday" that will of course get all of your Mondays to the top.
Then as we started to talk about this we realized that what they're really trying to do is figure out the day before the transaction.
The "banking" day before the transaction so they had, you know, some sort of a huge IF statement they said, Hey If it's Monday, we need to back this up two days and oh by the way there's like these 10 bank holidays that really tend to cause problems.
So, If we want to figure out the transaction date as the banking day before today, what we can do is use the =WORKDAY function.
Work day usually is used to figure out well from February 12 2007, I want to go - 1 work days, factoring in these bank holidays.
So over here in Column k, I type the bank holidays I always want to point to those bank holidays, so I'll hit F4 to put the $ in and that gives me the right answer in the wrong format.
Now we'll use "Format" "Cells" and I'll choose date.
Click OK.
so there we see that the banking day before the 12th, was Friday the 9th.
Will copy that down and It will correctly calculate all of the transaction days as one banking day before the original day.
Great use of excel.
The WORKDAY function, I realize is really obscure.
It's actually in the analysis toolpak you have to use "tools" "add-ins" and choose "Analysis Toolpak" to have that be available to you and then the obscure "dddd" function which shows us actual the day of the week instead of the date itself.
So a couple of great tips there. Thanks to the folks down in Charlotte for asking me that question in our seminar.
and thanks to you for stopping by. We'll see you next time for another netcast from MrExcel.
Today we have a question that came in one of my excel seminars.
It was a question from somebody who works at a bank and they said, Hey we're always dealing with bank holidays and with basically figuring out what day of the week something fell on and I said, okay.
Well, you know you don't have to do any formula at all.
First of all if you have a series of dates, you can choose that series of dates and go into "Format" "Cells" and of course there's a whole bunch of date functions here.
None of which are exactly what you want.
But if we go into custom and basically what happens is when you put in a single d or dd, you're just going to get the day of the month.
So for example, here February 23rd is going to give me a 23 but if I put in ddd, it's going to put Fri and dddd will give me Friday. So let's just format that column and basically say, Hey use the 4D format.
dddd and it will show me the day of the week.
Now if you need to do this with a formula.
We can say that we want to use the text =TEXT of cell A3 and what format we want to use.
Of course it's the "dddd" Close that quote, close the parentheses,copy down and we get the exact same value.
So there, there's a formula that will calculate that Now I had to point out that if you need to sort it by day of the week interesting tool "Data", "Sort" Sort by "Day of the week" but then say "options" and then instead of using a normal sort order we want to sort it into "Sunday Monday Tuesday" that will of course get all of your Mondays to the top.
Then as we started to talk about this we realized that what they're really trying to do is figure out the day before the transaction.
The "banking" day before the transaction so they had, you know, some sort of a huge IF statement they said, Hey If it's Monday, we need to back this up two days and oh by the way there's like these 10 bank holidays that really tend to cause problems.
So, If we want to figure out the transaction date as the banking day before today, what we can do is use the =WORKDAY function.
Work day usually is used to figure out well from February 12 2007, I want to go - 1 work days, factoring in these bank holidays.
So over here in Column k, I type the bank holidays I always want to point to those bank holidays, so I'll hit F4 to put the $ in and that gives me the right answer in the wrong format.
Now we'll use "Format" "Cells" and I'll choose date.
Click OK.
so there we see that the banking day before the 12th, was Friday the 9th.
Will copy that down and It will correctly calculate all of the transaction days as one banking day before the original day.
Great use of excel.
The WORKDAY function, I realize is really obscure.
It's actually in the analysis toolpak you have to use "tools" "add-ins" and choose "Analysis Toolpak" to have that be available to you and then the obscure "dddd" function which shows us actual the day of the week instead of the date itself.
So a couple of great tips there. Thanks to the folks down in Charlotte for asking me that question in our seminar.
and thanks to you for stopping by. We'll see you next time for another netcast from MrExcel.
