# Calculate No of Working days between two specific days excluding weekends

• Hi Friends,

Friends when we input the date in "A1", it has to calculate No of Working days from given date till TODAY and if the No. of Working days is below 1 to 10 then it should shows "Aging 1 to 10",
and if No. of Working days is between 11 to 20 then it should shows "Aging 11 to 20" and if No. of Working days is between" >20" then it should shows as "Aging more than 20"

Kindly help..,

Assume Today's date is 06/21/2018, So if input is 06/01/2018 then the difference is "13 Working days" (excluding Saturday/Sunday) so the Output will be "Aging 11 to 20"
[TABLE="border: 1, cellpadding: 1, width: 500"]

[tr]

[td]

Column A

[/td]

[td]

Column B

[/td]

[/tr]

[tr]

[td]

06/01/2018

[/td]

[td]

Aging 11 to 20

[/td]

[/tr]

[tr]

[td]

06/18/2018

[/td]

[td]

Aging 1 to 10

[/td]

[/tr]

[/TABLE]

With Regards,
Aswinraj A

• Have a look at the NetWorkDays() function.

Where there is a will there are many ways. Finding one that works for you is the challenge!

• [USER="14282"]NBVC[/USER] I tried few formulas using NewWorkDays function but it doenst works., Can you please help me out.

With Regards
Aswinraj A

• Assuming input date is in A2, you can try something like:

="Aging " &LOOKUP(NETWORKDAYS(A2,TODAY()),{1,11,21,31},{"1 to 10","11 to 20","21 to 30","over 30"})

where the first set of parameters in { } brackets represent the lower bounds of each date range and the second set of parameters are the respective return messages.

Where there is a will there are many ways. Finding one that works for you is the challenge!

• I know this was 2 years old but if you haven't got a solution and you would still like one then I have one for you. Let me know.
Regards

• It is a week old ... :stare:

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• Ooops! my mistake. :0ops:
Quite new to Forum activity anywhere.
Will compose my possible solution and post shortly.
Regards
legin52

• OK first step is to create a table of weekend dates (and or Holidays).
somewhere possibly on a new sheet which can be hidden later if required, in cell A1 (for example) Type in the date 01/01/2018 in B1 type in the day on which your chosen date fell in 2018 it was a Monday then by highlighting the 2 cells and grabbing the autofill cross and dragging down to a date you want, say 31/12/2018. This will give you a calendar. now apply a filter for Monday through Friday to be visible then delete them. This will leave you a list of all the dates for all the Saturdays and Sundays through the period.
At this point I would make use of the name manager to use in the later formula.
This has the added benefit of you being able to add specific bank holidays such as Easter and Christmas (by doing it manually if required.
Now I am going to send the sheet I created so you can see the formula and my construction might be of help.

Hope it helps
Regards
legin52 [ATTACH]n1204944[/ATTACH]

## Files

• [USER="14282"]NBVC[/USER] The solution which you gave worked.., Thank you..,

Others thanks for your ideas to help me out.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!