excel formula to subtract 2 of 7 days a week

To set up the conditional formatting rule, the steps are: Where A2 is the upper-left cell of the selected range. If some regional office of your organization works on a different schedule where the days of rest are other than Saturday and Sunday, you can easily adjust the WEEKDAY formula to your needs by specifying a different return_type.įor example, to treat Saturday and Monday as weekends, set return_type to 12, so you'll get the 'Tuesday (1) to Monday (7)' week type: In the screenshot below, we have weekdays filtered out, so only weekends are visible: =IF(WEEKDAY(A3, 2) Filter) and select either 'Weekend' or 'Workday'. To identify weekends and weekdays in Excel, build an IF statement with the nested WEEKDAY function. When dealing with a long list of dates, you may want to know which ones are working days and which are weekends. Excel WEEKDAY formula to find and filter workdays and weekends For example, the code format 'dddd, mmmm d, yyyy' will have the date displayed as ' Friday, January 3, 2025' while 'dddd' will show just ' Friday'. Another easy way to convert a date to the weekday name is by applying a custom date format. To get full day names, use the 'dddd' format code: To turn the weekday number into the day name, employ the TEXT function. How to convert Excel date to weekday nameīy design, the Excel WEEKDAY function returns the day of the week as a number. But let's look at it from a different angle and discuss some formulas that solve real-life tasks. Depending on the value set in the second argument, the formulas output different results.Īt first sight, it may seem that the numbers returned by the WEEKDAY function have very little practical sense. In the image below, all the formulas return the day of the week corresponding to January 1, 2025, which is stored as the number 45658 internally in Excel. For example, to start counting days from Monday, the formula is: To use the day mapping other than the default Sun-Sat, enter an appropriate number in the second argument. Or, supply the source date in a 100% reliable way using the DATE function: Just be sure to use the date format that Excel expects and can interpret: brought by the DATEVALUE function), you can enter that number directly in the formula:Īlso, you can type the date as a text string enclosed in quotation marks directly in the formula. If you have a serial number representing the date (e.g. Basic WEEKDAY formula in Excelįor starters, let's see how to use the WEEKDAY formula in its simplest form to get the day number from date.įor example, to get the weekday from date in C4 with the default Sunday - Saturday week, the formula is: The return_type values 11 through 17 were introduced in Excel 2010 and therefore they cannot be used in earlier versions.

Excel formula to subtract 2 of 7 days a week