Change WEEKDAY, NETWORKDAYS functions for other cultures

**tpillow** · 07-29-2009, 08:26 AM

Here in Dubai, the NETWORKDAYS function does not work properly because our workweek runs from Sunday-Thursday. Is there a way to modify the function to understand a different kind of calendar?

**oldchippy** · 07-29-2009, 09:19 AM

Hi,

Does this work for you?

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(A1&":"&B1)),"dddd")<>"Friday"),--(TEXT(ROW(INDIRECT(A1&":"&B1)),"dddd")<>"Saturday"))

A1 being the start date and B1 the end date

**DonkeyOte** · 07-29-2009, 09:24 AM

FYI: I have moved this thread from Non-English forum to Worksheet Functions Forum (the former being for question written in other languages)

**oldchippy** · 07-29-2009, 09:29 AM

Originally Posted by DonkeyOte

FYI: I have moved this thread from Non-English forum to Worksheet Functions Forum (the former being for question written in other languages)

Probably the best place for it - thanks

**tpillow** · 07-30-2009, 07:16 AM

Old Chippy - Thanks a lot, that actually does serve the purpose of NETWORKDAYS and gives me an accurate calculation. Brilliant! And it certainly solves my immediate task. It's also flexible enough for me to tweak in a variety of ways. Going to be very useful.

Perhaps I had not been clear, about one thing, however. Since this will certainly not be the last time I'll have to use various day-of-the-week functions that will be thrown off by our calendar here, is anyone aware of a more general solution, either with VBA or perhaps a setting somewhere, that will get Excel to treat different days as week days if I so choose?

Thanks in advance....

**arthurbr** · 07-30-2009, 07:35 AM

Hi,

does this link help?

**DonkeyOte** · 07-30-2009, 07:37 AM

also, a search on this board of NETWORKDAYS alternatives with daddylonglegs as poster is bound to throw up a variety of examples.

**daddylonglegs** · 07-30-2009, 07:39 AM

I'm not aware of any general setting you can change.....

If your work week is still 5 consecutive days then you can easily modify NETWORKDAYS, e.g. for Sunday to Thursday

=NETWORKDAYS(A1+1,B1+1)

[If you want Saturday to Wednesday its +2, +3 for Friday to Tuesday etc.]

If you want to exclude holidays too then the formula needs to be "array entered"

=NETWORKDAYS(A1+1,B1+1,H$1:H$10+1)

confirmed with CTRL+SHIFT+ENTER

where H1:H10 contain holiday dates

You can also use a formula like this to count Sunday to Thursday

=SUM(INT((WEEKDAY(A1-{1,2,3,4,5})+B1-A1)/7))

{1,2,3,4,5} defines the days to count (1= Sun through to 7 = Sat) so you can modify in any way you want to count any combination of days, e.g. Monday and Thursday only....

=SUM(INT((WEEKDAY(A1-{2,5})+B1-A1)/7))

but this approach is less adaptable should you wish to exclude holidays also