Spreadsheet: 114706d1310441652-calculating-dates-with-some-condition-date-calculation
Sheet: Template

Example |  |  |  |  |  |  | Date | Day | Caculating Date
 |  |  |  |  |  |  | 2011-04-01 00:00:00 | Friday | 16 |  | D-16
D | 2011-10-06 00:00:00 |  |  |  |  |  | 2011-04-02 00:00:00 | Saturday | 15 |  | D-15
D-1 | 2011-09-06 00:00:00 |  |  |  |  |  | 2011-04-03 00:00:00 | Sunday | 14 |  | D-14
D-5 | 2011-03-06 00:00:00 | 2011-04-06 00:00:00 | 2011-05-06 00:00:00 |  |  |  | 2011-04-04 00:00:00 | Monday | 14 |  | D-14
D-9 | 27/05/2011 | 28/05/201 | 29/05/2011 | 30/05/2011 |  |  | 2011-04-05 00:00:00 | Tuesday | 14 |  | D-14
D-11 | 25/05/2011 |  |  |  |  |  | 2011-04-06 00:00:00 | Wednesday | 13 |  | D-13
D-30 | 27/042011 |  |  |  |  |  | 2011-04-07 00:00:00 | Thursday | 12 |  | D-12
 |  |  |  |  |  |  | 2011-04-08 00:00:00 | Friday | 11 |  | D-11
Template |  |  |  |  |  |  | 2011-04-09 00:00:00 | Saturday | 10 |  | D-10
 |  |  |  |  |  |  | 2011-04-10 00:00:00 | Sunday | 9 |  | D-9 | D-9 is 10/06/2011 , 11/06/2011 ,12/06/2011
D |  |  |  |  |  |  | 2011-04-11 00:00:00 | Monday | 9 |  | D-9
D-1 |  |  |  |  |  |  | 2011-04-12 00:00:00 | Tuesday | 9 |  | D-9
D-5 |  |  |  |  |  |  | 2011-04-13 00:00:00 | Wednesday | 8 |  | D-8
D-9 |  |  |  |  |  |  | 2011-04-14 00:00:00 | Thursday | 7 |  | [NAME] | [NAME] is 14/06/2011
D-11 |  |  |  |  |  |  | 2011-04-15 00:00:00 | Friday | 6 |  | D-6
D-30 |  |  |  |  |  |  | 2011-04-16 00:00:00 | Saturday | 5 |  | D-5
 |  |  |  |  |  |  | 2011-04-17 00:00:00 | Sunday | 4 |  | D-4 | D-4 is 17/06/2011 , 18/06/2011 ,19/06/2011
Dates in mm/dd/yyyy format | Days difference from today's date |  |  | Bank holidays till 2012 in dd/mm/yyyy format |  |  | 2011-04-18 00:00:00 | Monday | 4 |  | D-4
2011-04-01 00:00:00 |  |  |  | 2011-03-01 00:00:00 |  |  | 2011-04-19 00:00:00 | Tuesday | 4 |  | D-4
2011-04-02 00:00:00 |  |  |  | 22/04/2011 |  |  | 2011-04-20 00:00:00 | Wednesday | 3 |  | D-3
2011-04-03 00:00:00 |  |  |  | 25/04/2011 |  |  | 2011-04-21 00:00:00 | Thursday | 2 |  | D-2 | D-2 is 21/06/2011
2011-04-04 00:00:00 |  |  |  | 29/04/2011 |  |  | 2011-04-22 00:00:00 | Friday | 1 |  | D-1
2011-04-05 00:00:00 |  |  |  | 2011-02-05 00:00:00 |  |  | 2011-04-23 00:00:00 | Saturday | 0 |  | D | D is today's date
2011-04-06 00:00:00 |  |  |  | 30/05/2011 |  |  | 2011-04-24 00:00:00 | Sunday
2011-04-07 00:00:00 |  |  |  | 29/08/2011 |  |  | 2011-04-25 00:00:00 | Monday
2011-04-08 00:00:00 |  |  |  | 26/12/2011 |  |  | 2011-04-26 00:00:00 | Tuesday
2011-04-09 00:00:00 |  |  |  | 27/12/2011 |  |  | 2011-04-27 00:00:00 | Wednesday
2011-04-10 00:00:00 |  |  |  | 2012-02-01 00:00:00 |  |  | 2011-04-28 00:00:00 | Thursday
2011-04-11 00:00:00 |  |  |  | 2012-06-04 00:00:00 |  |  | 2011-04-29 00:00:00 | Friday
2011-04-12 00:00:00 |  |  |  | 2012-09-04 00:00:00 |  |  | 2011-04-30 00:00:00 | Saturday
2011-04-13 00:00:00 |  |  |  | 2012-07-05 00:00:00 |  |  | 2011-05-01 00:00:00 | Sunday
2011-04-14 00:00:00 |  |  |  | 28/05/2012 |  |  | 2011-05-02 00:00:00 | Monday
2011-04-15 00:00:00 |  |  |  | 27/08/2012 |  |  | 2011-05-03 00:00:00 | Tuesday
2011-04-16 00:00:00 |  |  |  | 25/12/2012 |  |  | 2011-05-04 00:00:00 | Wednesday
2011-04-17 00:00:00 |  |  |  | 26/12/2012 |  |  | 2011-05-05 00:00:00 | Thursday
2011-04-18 00:00:00 |  |  |  |  |  |  | 2011-05-06 00:00:00 | Friday
2011-04-19 00:00:00 |  |  |  |  |  |  | 2011-05-07 00:00:00 | Saturday
2011-04-20 00:00:00 |  |  |  |  |  |  | 2011-05-08 00:00:00 | Sunday
2011-04-21 00:00:00 |  |  |  |  |  |  | 2011-05-09 00:00:00 | Monday
2011-04-22 00:00:00 |  |  |  |  |  |  | 2011-05-10 00:00:00 | Tuesday
2011-04-23 00:00:00 |  |  |  |  |  |  | 2011-05-11 00:00:00 | Wednesday
2011-04-24 00:00:00 |  |  |  |  |  |  | 2011-05-12 00:00:00 | Thursday
2011-04-25 00:00:00 |  |  |  |  |  |  | 2011-05-13 00:00:00 | Friday
2011-04-26 00:00:00 |  |  |  |  |  |  | 2011-05-14 00:00:00 | Saturday
2011-04-27 00:00:00 |  |  |  |  |  |  | 2011-05-15 00:00:00 | Sunday
2011-04-28 00:00:00 |  |  |  |  |  |  | 2011-05-16 00:00:00 | Monday
2011-04-29 00:00:00 |  |  |  |  |  |  | 2011-05-17 00:00:00 | Tuesday
2011-04-30 00:00:00 |  |  |  |  |  |  | 2011-05-18 00:00:00 | Wednesday
2011-05-01 00:00:00 |  |  |  |  |  |  | 2011-05-19 00:00:00 | Thursday
2011-05-02 00:00:00 |  |  |  |  |  |  | 2011-05-20 00:00:00 | Friday
2011-05-03 00:00:00 |  |  |  |  |  |  | 2011-05-21 00:00:00 | Saturday
2011-05-04 00:00:00 |  |  |  |  |  |  | 2011-05-22 00:00:00 | Sunday
2011-05-05 00:00:00 |  |  |  |  |  |  | 2011-05-23 00:00:00 | Monday
2011-05-06 00:00:00 |  |  |  |  |  |  | 2011-05-24 00:00:00 | Tuesday
2011-05-07 00:00:00 |  |  |  |  |  |  | 2011-05-25 00:00:00 | Wednesday
2011-05-08 00:00:00 |  |  |  |  |  |  | 2011-05-26 00:00:00 | Thursday
2011-05-09 00:00:00 |  |  |  |  |  |  | 2011-05-27 00:00:00 | Friday
2011-05-10 00:00:00 |  |  |  |  |  |  | 2011-05-28 00:00:00 | Saturday
2011-05-11 00:00:00 |  |  |  |  |  |  | 2011-05-29 00:00:00 | Sunday
2011-05-12 00:00:00 |  |  |  |  |  |  | 2011-05-30 00:00:00 | Monday
2011-05-13 00:00:00 |  |  |  |  |  |  | 2011-05-31 00:00:00 | Tuesday
2011-05-14 00:00:00 |  |  |  |  |  |  | 2011-06-01 00:00:00 | Wednesday
2011-05-15 00:00:00 |  |  |  |  |  |  | 2011-06-02 00:00:00 | Thursday
2011-05-16 00:00:00 |  |  |  |  |  |  | 2011-06-03 00:00:00 | Friday
2011-05-17 00:00:00 |  |  |  |  |  |  | 2011-06-04 00:00:00 | Saturday
2011-05-18 00:00:00 |  |  |  |  |  |  | 2011-06-05 00:00:00 | Sunday
2011-05-19 00:00:00 |  |  |  |  |  |  | 2011-06-06 00:00:00 | Monday
2011-05-20 00:00:00 |  |  |  |  |  |  | 2011-06-07 00:00:00 | Tuesday
2011-05-21 00:00:00 |  |  |  |  |  |  | 2011-06-08 00:00:00 | Wednesday
2011-05-22 00:00:00 |  |  |  |  |  |  | 2011-06-09 00:00:00 | Thursday
2011-05-23 00:00:00 |  |  |  |  |  |  | 2011-06-10 00:00:00 | Friday
2011-05-24 00:00:00 |  |  |  |  |  |  | 2011-06-11 00:00:00 | Saturday
2011-05-25 00:00:00 |  |  |  |  |  |  | 2011-06-12 00:00:00 | Sunday
2011-05-26 00:00:00 |  |  |  |  |  |  | 2011-06-13 00:00:00 | Monday
2011-05-27 00:00:00 |  |  |  |  |  |  | 2011-06-14 00:00:00 | Tuesday
2011-05-28 00:00:00 |  |  |  |  |  |  | 2011-06-15 00:00:00 | Wednesday
2011-05-29 00:00:00 |  |  |  |  |  |  | 2011-06-16 00:00:00 | Thursday
2011-05-30 00:00:00 |  |  |  |  |  |  | 2011-06-17 00:00:00 | Friday
2011-05-31 00:00:00 |  |  |  |  |  |  | 2011-06-18 00:00:00 | Saturday
2011-06-01 00:00:00 |  |  |  |  |  |  | 2011-06-19 00:00:00 | Sunday
2011-06-02 00:00:00 |  |  |  |  |  |  | 2011-06-20 00:00:00 | Monday
2011-06-03 00:00:00 |  |  |  |  |  |  | 2011-06-21 00:00:00 | Tuesday
2011-06-04 00:00:00 |  |  |  |  |  |  | 2011-06-22 00:00:00 | Wednesday
2011-06-05 00:00:00 |  |  |  |  |  |  | 2011-06-23 00:00:00 | Thursday
2011-06-06 00:00:00 |  |  |  |  |  |  | 2011-06-24 00:00:00 | Friday
2011-06-07 00:00:00 |  |  |  |  |  |  | 2011-06-25 00:00:00 | Saturday
2011-06-08 00:00:00 |  |  |  |  |  |  | 2011-06-26 00:00:00 | Sunday
2011-06-09 00:00:00 |  |  |  |  |  |  | 2011-06-27 00:00:00 | Monday
2011-06-10 00:00:00 |  |  |  |  |  |  | 2011-06-28 00:00:00 | Tuesday
2011-06-11 00:00:00 |  |  |  |  |  |  | 2011-06-29 00:00:00 | Wednesday
2011-06-12 00:00:00 |  |  |  |  |  |  | 2011-06-30 00:00:00 | Thursday
2011-06-13 00:00:00 |  |  |  |  |  |  | 2011-07-01 00:00:00 | Friday
2011-06-14 00:00:00 |  |  |  |  |  |  | 2011-07-02 00:00:00 | Saturday
2011-06-15 00:00:00 |  |  |  |  |  |  | 2011-07-03 00:00:00 | Sunday
2011-06-16 00:00:00 |  |  |  |  |  |  | 2011-07-04 00:00:00 | Monday
2011-06-17 00:00:00 |  |  |  |  |  |  | 2011-07-05 00:00:00 | Tuesday
2011-06-18 00:00:00 |  |  |  |  |  |  | 2011-07-06 00:00:00 | Wednesday
2011-06-19 00:00:00 |  |  |  |  |  |  | 2011-07-07 00:00:00 | Thursday
2011-06-20 00:00:00 |  |  |  |  |  |  | 2011-07-08 00:00:00 | Friday
2011-06-21 00:00:00 |  |  |  |  |  |  | 2011-07-09 00:00:00 | Saturday
2011-06-22 00:00:00 |  |  |  |  |  |  | 2011-07-10 00:00:00 | Sunday
2011-06-23 00:00:00 |  |  |  |  |  |  | 2011-07-11 00:00:00 | Monday
2011-06-24 00:00:00 |  |  |  |  |  |  | 2011-07-12 00:00:00 | Tuesday
2011-06-25 00:00:00 |  |  |  |  |  |  | 2011-07-13 00:00:00 | Wednesday
2011-06-26 00:00:00 |  |  |  |  |  |  | 2011-07-14 00:00:00 | Thursday
2011-06-27 00:00:00 |  |  |  |  |  |  | 2011-07-15 00:00:00 | Friday
2011-06-28 00:00:00 |  |  |  |  |  |  | 2011-07-16 00:00:00 | Saturday
2011-06-29 00:00:00 |  |  |  |  |  |  | 2011-07-17 00:00:00 | Sunday
2011-06-30 00:00:00 |  |  |  |  |  |  | 2011-07-18 00:00:00 | Monday
2011-07-01 00:00:00 |  |  |  |  |  |  | 2011-07-19 00:00:00 | Tuesday
2011-07-02 00:00:00 |  |  |  |  |  |  | 2011-07-20 00:00:00 | Wednesday
2011-07-03 00:00:00 |  |  |  |  |  |  | 2011-07-21 00:00:00 | Thursday
2011-07-04 00:00:00 |  |  |  |  |  |  | 2011-07-22 00:00:00 | Friday
2011-07-05 00:00:00 |  |  |  |  |  |  | 2011-07-23 00:00:00 | Saturday
2011-07-06 00:00:00 |  |  |  |  |  |  | 2011-07-24 00:00:00 | Sunday
2011-07-07 00:00:00 |  |  |  |  |  |  | 2011-07-25 00:00:00 | Monday
2011-07-08 00:00:00 |  |  |  |  |  |  | 2011-07-26 00:00:00 | Tuesday
2011-07-09 00:00:00 |  |  |  |  |  |  | 2011-07-27 00:00:00 | Wednesday
2011-07-10 00:00:00 |  |  |  |  |  |  | 2011-07-28 00:00:00 | Thursday
2011-07-11 00:00:00 |  |  |  |  |  |  | 2011-07-29 00:00:00 | Friday
2011-07-12 00:00:00 |  |  |  |  |  |  | 2011-07-30 00:00:00 | Saturday
2011-07-13 00:00:00 |  |  |  |  |  |  | 2011-07-31 00:00:00 | Sunday
2011-07-14 00:00:00 |  |  |  |  |  |  | 2011-08-01 00:00:00 | Monday
2011-07-15 00:00:00 |  |  |  |  |  |  | 2011-08-02 00:00:00 | Tuesday
2011-07-16 00:00:00 |  |  |  |  |  |  | 2011-08-03 00:00:00 | Wednesday
2011-07-17 00:00:00 |  |  |  |  |  |  | 2011-08-04 00:00:00 | Thursday
2011-07-18 00:00:00 |  |  |  |  |  |  | 2011-08-05 00:00:00 | Friday
 |  |  |  |  |  |  | 2011-08-06 00:00:00 | Saturday
 |  |  |  |  |  |  | 2011-08-07 00:00:00 | Sunday
 |  |  |  |  |  |  | 2011-08-08 00:00:00 | Monday
 |  |  |  |  |  |  | 2011-08-09 00:00:00 | Tuesday
 |  |  |  |  |  |  | 2011-08-10 00:00:00 | Wednesday
 |  |  |  |  |  |  | 2011-08-11 00:00:00 | Thursday
 |  |  |  |  |  |  | 2011-08-12 00:00:00 | Friday
 |  |  |  |  |  |  | 2011-08-13 00:00:00 | Saturday
 |  |  |  |  |  |  | 2011-08-14 00:00:00 | Sunday
 |  |  |  |  |  |  | 2011-08-15 00:00:00 | Monday
 |  |  |  |  |  |  | 2011-08-16 00:00:00 | Tuesday
 |  |  |  |  |  |  | 2011-08-17 00:00:00 | Wednesday
 |  |  |  |  |  |  | 2011-08-18 00:00:00 | Thursday
 |  |  |  |  |  |  | 2011-08-19 00:00:00 | Friday
 |  |  |  |  |  |  | 2011-08-20 00:00:00 | Saturday
 |  |  |  |  |  |  | 2011-08-21 00:00:00 | Sunday
 |  |  |  |  |  |  | 2011-08-22 00:00:00 | Monday
 |  |  |  |  |  |  | 2011-08-23 00:00:00 | Tuesday
 |  |  |  |  |  |  | 2011-08-24 00:00:00 | Wednesday