Look Who it Was Random video

Excel - Master Excel Date Functions: Formatting, Sorting, & Calculating Banking Days - Episode 644

533 views · Published 26 March 2009 · 3:21 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  Master Excel Date Functions: Formatting, Sorting, & Calculating Banking Days.

Welcome to another MrExcel netcast! In today's video, we'll be discussing a question that came up during one of my Excel seminars. It was from someone who works at a bank and they were struggling with calculating dates and figuring out what day of the week a transaction fell on. Well, I have some great tips and tricks to share with you that will make this process a lot easier.

First, did you know that you can easily format a series of dates to show the day of the week? Simply select the dates, go to "Format" and then "Cells". From there, choose the "Custom" option and enter "dddd" to display the day of the week. This is a quick and easy way to see the day of the week for a series of dates without using any formulas.

But if you do need to use a formula, don't worry, I have you covered. You can use the =TEXT function to display the day of the week for a specific date. Just enter the cell reference and the format you want, such as "dddd". This will give you the same result as the custom formatting method we just discussed. And if you need to sort your data by day of the week, I'll show you a handy trick using the "Data" tab and the "Sort" function.

But what if you need to calculate the previous banking day for a transaction? This is where the =WORKDAY function comes in. This function takes into account bank holidays and will give you the correct date for the previous banking day. Just make sure to have the Analysis Toolpak add-in enabled to access this function. And for those of you who are curious, I'll also show you how to use the obscure "dddd" function to display the day of the week instead of the date itself.

I hope these tips and tricks will make your life easier when dealing with dates and bank holidays in Excel. A big thank you to the folks in Charlotte for asking this question during our seminar. And as always, thank you for watching and don't forget to subscribe to our channel for more helpful Excel tips and tricks. See you next time on the MrExcel netcast!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


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!

Table of Contents:
(00:00) Formatting Dates
(00:33) Using Custom Format
(01:06) Using the TEXT Formula
(01:23) Sorting by Day of the Week
(02:00) Using the WORKDAY Function
(02:13) Adding Bank Holidays
(02:23) Formatting the Result
(02:41) Using the Analysis Toolpak
(02:51) Using the "dddd" Function
(03:01) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Analysis Toolpak
Bank holidays
Custom format
Date functions
Day of the week
Excel seminars
Format cells
IF statement
Sort by day of the week
Sunday Monday Tuesday sort order
Text formula
WORKDAY function


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152284/

More from this channel