weekend length according to regional settings

New Contributor

Excel has the build in option of filling in series of weekdays, meaning it will skip the weekends. In order to make Excel skip two days for weekends I have to change my PC regional settings. I live in Israel and Excel considers my weekends to be only one day. In Power Query there is an option of changing Data Types according to "Using Locale...". Is there somewhere in Excel a simular option? Is it possible to change the default length of weekends which is apperently connected to my regional settings without changing my regional settings?

2 Replies

@YosefK 

Fill >Series always uses the regional settings. You can use a formula though.

Let's say you enter the first date in D1.

In D2, enter the formula =WORKDAY.INTL(D1,1,7) and fill down as far as you want.

Using 7 as 3rd argument will use Friday and Saturday as weekend days.

See WORKDAY.INTL function for more info.

Thanks for your fast reply. Indeed your workaround works but I was hoping that it would be possible to use the "fill in series" without having to change my pc regional settings, the way I can do that in PQ. Also in the TEXT formula I can circumvent the regional settings for example =TEXT(TODAY(),[$-nl-NL],mmmm) will return the name of the month in Dutch eventhough my regional settings are set to Israel.