Solved

change weekend days in Excel

Posted on 2014-02-02
3
1,628 Views
Last Modified: 2014-02-05
Hi

In my country weekends are Friday and Saturday instead of Sat and Sun. So, how can change it in Excel due to I have some functions to calculate working days ??


Thanks
0
Comment
Question by:obad62
3 Comments
 
LVL 18

Assisted Solution

by:Steven Harris
Steven Harris earned 167 total points
ID: 39828068
Are you using networkwordays() by chance?  Switch to:

=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

In your case, [weekend] = 7, so - If start date is A1, end date is B2:

=NETWORKDAYS.INTL(A1,B1,7)
0
 
LVL 81

Accepted Solution

by:
byundt earned 167 total points
ID: 39828721
There is also WORKDAY.INTL, which is equivalent to WORKDAY but lets you specify the days of the weekend. Like NETWORKDAYS.INTL as mentioned by ThinkSpaceSolutions, your Friday & Saturday weekend would be specified by [weekend] = 7

To get a date 12 working days in the future, you would use:
=WORKDAY.INTL(TODAY(),12,7,HolidayList)          
where HolidayList is a named range listing public holidays in your country

NETWORKDAYS.INTL and WORKDAY.INTL require Excel 2010 or later.
1
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 166 total points
ID: 39829407
In earlier versions of Excel you can modify the regular NETWORKDAYS function to treat Friday and Saturday as weekend days by "offsetting" the dates, e.g. like this:

=NETWORKDAYS(A2+1,B2+1)

[+2 would give you Thurs/Fri weekend, +3 Wed/Thurs etc.]

With holidays in H2:H10 you can also exclude holidays with this version

=NETWORKDAYS(A2+1,B2+1,INDEX(H$2:H$10+1,0))

For WORKDAY try this

=WORKDAY(A2+1,C2)-1

where A2 is start date and C2 the number of days to add

Again you can exclude holidays with this version

=WORKDAY(A2+1,C2,INDEX(H$2:H$10+1,0))-1

regards, barry
1

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question