Previous Topic Next Topic

Calculating dates with some conditions

Closed
priyaa - Jun 11, 2011 at 06:48 AM
 Priyaa - Jun 18, 2011 at 03:01 AM
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 response

Answer 1 / 1
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 12, 2011 at 07:28 AM
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
priyaa
Jun 12, 2011 at 11:24 PM
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.
Priyaa
Jun 18, 2011 at 03:01 AM
Please ignore the previous link.

Use this link for excel worked example.

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

Similar discussions

encryption message several time in chat
measking -
HelpiOS -

1 response
IF statements with dates and criteria
jimbabama -
Mazzaropi -

3 responses
blank white page for website
yadaves -
ac3mark -

1 response
How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
using web browser page loads but screen blank
inchoate -
leo -

16 responses