Calculating dates with some conditions

Closed
Report
-
 Priyaa -
Hello,
Could you please help me in calculating some dates,
for ex: today's date ( D ) - 11/06/2011
I want to calculate dates for D+1, D+2.....D+30 with the help of a formula with some conditions,
1. Friday + Saturday + Sunday should be considered as a single day
2. If D+1 / D+5 / D+9...... is on Friday, i want Friday, Saturday and Sunday dates to be displayed.
3. If monday is a bank holiday, then Friday + Saturday + Sunday + Monday should be considered as one day and if D+1 / D+5 / D+9...... is on friday, i want friday, saturday , sunday and monday dates to be displayed.
4. If friday is a bank holiday, that friday also has to be considered while calculating dates.
5. If friday and monday are bank holidays, then friday + saturday +sunday + monday should be considered as one day.
Please find 2011 and 2012 calender with bank holidays in sheet 2. ( yellow coloured cells are bank holidays)
I want these days to be calculated till 2012 end.
Also could you please giude me how to protect formulas in excel, so that only 'D' field should be able to type and no ammendment should be done in rest cells.

http://www.speedyshare.com/files/28912864/Date_calculation.xls
Please use this link for excel.
Thanks in advance.

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Even though you did explain a lot and provided a sample sheet, i am still having a hard time understanding. Are you basically looking to find number of days between two days and exclude any holidays, I have a feeling you would need to use NETWORKDAYS function in some way to get to what you want. For other part you can lock the cells by protecting the sheet
Hi,

Thank you for your reply.

I am sorry that i made some mistake while explaining.

I want to find the date for the following,

D-1 , D-5 , D-9 , D-11 , D-30.

Here D is today's date.

(-) is minus symbol.

D-1 is yesterday's date.

D-5 ---> Date before 5 days from today's date, calculating from yesterday's date.

Same way for D-11 and D-30.

Could you please find the below link. one example has been worked.

http://www.speedyshare.com/files/28912864/Date_calculation.xls.

I hope this clear now.

Thank you.
Please ignore the previous link.

Use this link for excel worked example.

http://www.speedyshare.com/files/29027444/Date_calculation.xls