![]() ![]() Select 'Use a formula to determine which cells to format'.ĥ. On the Home tab, in the Styles group, click Conditional Formatting.Ĥ. You can use conditional formatting in Excel to highlight dates that are weekdays (Monday, Tuesday, Wednesday, Thursday or Friday).Ģ. Select cell A2 and drag the fill handle down.Įxplanation: the calendar below helps you understand this formula.Ĭonclusion: this formula automatically skips Mondays, Sundays and holidays. In this example, Monday and Sunday are weekend days.ģ. The second 0 tells Excel that Tuesday is not a weekend day, etc. The first 1 tells Excel that Monday is a weekend day. Enter the WORKDAY.INTL function shown below.Įxplanation: use a string of 0's and 1's (third argument) to specify which days are weekend days. Enter the date into cell A1 and specify a list of holidays (E1:E2).Ģ. ![]() Let's create a list of dates excluding Mondays, Sundays and holidays.ġ. Use WORKDAY.INTL instead of WORKDAY to specify different weekend days. If you supply a list of holidays, the WORKDAY function also excludes holidays.Įxplanation: there are 11 circles in the calendar shown below. The calendar below helps you understand this formula. Įxplanation: the WORKDAY function does not include the start date. The WORKDAY function below returns the date. WORKDAY excludes weekends (Saturday and Sunday).ġ. The WORKDAY function in Excel returns the date before or after a specified number of workdays. If you supply a list of holidays, the NETWORKDAYS function also excludes holidays.Įxplanation: simply count the number of circles in the calendar below. Įxplanation: the NETWORKDAYS function includes the start date and the end date. The NETWORKDAYS function below returns 11. NETWORKDAYS excludes weekends (Saturday and Sunday).ġ. The NETWORKDAYS function in Excel returns the number of workdays between two dates. Or create a custom date format ( dddd) to display the day of the week. You can also use the TEXT function to display the day of the week.ģ. *in fact it is a Monday but Excel was written to match the incorrect dates in Lotus 1-2-3 which treated 1900 as a leap year when it is not.2. A true text field is passed 'as is' and displays properly in Word. Using a second column and a TEXT function is essential if you want to use the weekday explicitly somewhere in a mail merge (for example), similarly for things like currencies and so on Excel > Word merging passes the actual underlying stored value rather than the on-screen formatted version, so regardless of the cell format, Word sees some horrible number. To give eg Sat so the date is explicit but shows the weekday name as well. I often use date formats such as ddd dd mmm yyyy ![]() You could of course just format the date cells themselves with a custom format as already suggested, depending on whether you really need this in a separate column or not. You can make the formula shorter, faster and more robust simply by using =TEXT(A1,"dddd") (yes I know hardly anyone uses that, but you don't want to build a solution which relies on that do you?) ![]() This formula will break if someone opens it who has the option selected to use the 1904-based date system, as was not a Sunday, but a Friday. When you then format this as "dddd" you are actually getting the day name of the 7th day in Excel since its "epoch", ie, which happens to be a Saturday*. Eg if your date is and you use the WEEKDAY function, this results in 7 (=Saturday). What you are actually calculating in that method is the day of the week as a number, then formatting that as a day based on that number interpreted as a date. The answer given above is only working by fluke because Excel thinks that was a Sunday* and by default Excel is using Sunday as first day of week for the Weekday function. ![]()
0 Comments
Leave a Reply. |