How to round date to previous or next specific weekday in Excel?
Round date to previous or next specific weekday
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
If you want to round dates to next Sunday, you can follow below steps.
1. Select a range that you will put the rounding dates in, and right click to select Format Cells from the context menu. See screenshot:
2. In the Format Cells dialog, under Number tab, click Date from the Category list, then select a type as you need. See screenshot:
3. Click OK. Then go to the first cell of the selected cells, and type this formula =IF(A2="","",A2+7-MOD(A2-1,7)) into it, and press Enter key then drag the autofill handle to the cells. See screenshot:
1. In the above formula =IF(A2="","",A2+7-MOD(A2-1,7)), A2 is the date you want to round, you can change the 7 to other number to round date to other weekday, 1 means next Monday, 2 means next Tuesday and so on.
2. If you want to round date to previous Sunday, you can use this formula =A2-WEEKDAY(A2,2)+7, then drag the autofill handle to cells you need. A2 is the date you want to round, and you can change 7 to 1,2,3,4,5,6 to round date to previous Monday, Tuesday, Wednesday, Thursday, Friday and Saturday.
Tabbed browsing & editing multiple Excel workbooks/Word documents as Firefox, Chrome, Internet Explore 10!
You may be familiar to view multiple webpages in Firefox/Chrome/IE, and switch between them by clicking corresponding tabs easily. Here, Office Tab supports similar processing, which allow you to browse multiple Excel workbooks or Word documents in one Excel window or Word window, and easily switch between them by clicking their tabs. Click for free 45-day trial of Office Tab!
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.
To post as a guest, your comment is unpublished.· 8 months agohi guys,
I used the formula below, I hope it works for you.
IF(WEEKDAY(H2,16)<>7,(7-WEEKDAY(H2,16))+H2,H2) where H2 is the date that you want to round up to the next day of the week (in my case for Friday) and the 16 is the format to start the date with Saturday as #1 and Friday as #7.
To post as a guest, your comment is unpublished.· 11 months agoYour round to previous Sunday is flawed. Even shows as much on your sheet.