- To post as a guest, your comment is unpublished.· 1 years agoSorry, I cannot understand, could you speak in English?
How to round date to previous or next specific weekday in Excel?
Round date to previous or next specific weekday
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.
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.· 1 years agoSi quiero la fecha del próximo viernes cómo debo modificar las variables? Gracias
- To post as a guest, your comment is unpublished.· 2 years 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.· 2 years agoYour round to previous Sunday is flawed. Even shows as much on your sheet.
- To post as a guest, your comment is unpublished.· 2 years agoSo, what would be the correct formula to first check to see if a cell has a date, else leave it blank and make the date the fall on a Friday before if the date falls on a Saturday or Sunday?
- To post as a guest, your comment is unpublished.· 2 days agoI found a workaround for to the previous Friday. You can change the switch "results" as needed to round to different days.=H3-SWITCH(TEXT(H3, "DDD"), "Mon", 3, "Tue", 4, "Wed", 5, "Thu", 6, "Fri", 0, "Sat", 1, "Sun", 2)=[date]-SWITCH(TEXT([date], "DDD"), "Mon", 3, "Tue", 4, "Wed", 5, "Thu", 6, "Fri", 0, "Sat", 1, "Sun", 2)