# Date Calculation

#### Dibble

##### New Member
Having made some attempts.. and some searching on this forum I find myself in need of the excellent help on this forum.

I am trying to create a spreadsheet that will automatically calculate a date in the future.

The future date calculated needs to take the time NOW (as the user completes a field) and determines if this is before MIDNIGHT on the following SUNDAY. If so it calculates the date as a Tuesday 2 weeks on. For example... I request a date now on 1st Jan which is a Thursday. This is before midnight Sunday and so the date calculated would be Tuesday 20th January.

If the time NOW is after MIDNIGHT Sunday and before the next TUESDAY.. ie 0001hrs on the Monday and 2359hrs on the Monday then the date returned should be Tuesday 3 weeks on.. e.g 27th.

It would also need a function that would allow the total number of referrals on a Tuesday to 20 at which point it would overspill and provide a date on the Friday of the same week.. so in the first example it would be Friday 23rd

Is this something that can be achieved with formulas and a set of dates for it to calculate from or do I need to find someone who can do it in VBA for me?

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
I think this formula will do what you want with regards to the Tuesdays where today’s date is in A1:
Code:
``=IF(WEEKDAY(A1,2)=1,A1+22,A1+14+(2-WEEKDAY(A1,2)))``

And this should take care of the Fridays:
Code:
``=IF(COUNTIF(\$B\$1:B1,IF(WEEKDAY(A2,2)=1,A2+22,A2+14+(2-WEEKDAY(A2,2))))<20,IF(WEEKDAY(A2,2)=1,A2+22,A2+14+(2-WEEKDAY(A2,2))),IF(WEEKDAY(A2,2)=1,A2+22,A2+14+(2-WEEKDAY(A2,2)))+3)``
On the basis that your table looks something like this:
Excel Workbook
ABCD
1
225/07/200707/08/2007
325/07/200707/08/2007
425/07/200707/08/2007
525/07/200707/08/2007
625/07/200707/08/2007
725/07/200707/08/2007
825/07/200707/08/2007
925/07/200707/08/2007
1025/07/200707/08/2007
1125/07/200707/08/2007
1225/07/200707/08/2007
1325/07/200707/08/2007
1425/07/200707/08/2007
1525/07/200707/08/2007
1625/07/200707/08/2007
1725/07/200707/08/2007
1825/07/200707/08/2007
1925/07/200707/08/2007
2025/07/200707/08/2007
2125/07/200707/08/2007
2225/07/200710/08/2007
2325/07/200710/08/2007
24
25
Sheet1

Really fast response. Thanks.

I've not moved onto the Friday date/formula just yet. I've put the Tuesday one in and it returns the date but one week too early. i.e... I've put today in and it returns the 4th August... This is a Sunday... it is hoped that the date returned for today would be... 14th August?

Error in my coding as I changed the cell ref to D5.. but hadn't replaced the last one in the forumla. Date now shows 7th August... so needs to be one week more.. i.e 14th.

Thanks v much

Try

=D5+23-WEEKDAY(D5,2)

Error in my coding as I changed the cell ref to D5.. but hadn't replaced the last one in the forumla. Date now shows 7th August... so needs to be one week more.. i.e 14th.

You'll need, in that case, to change the 14's to 21's and the 21's to 28's in the formula.

I changed the original code by Lewiy to show this

=IF(WEEKDAY(D5,2)=1,D5+22,D5+21+(2-WEEKDAY(D5,2)))

This now works fine and returns the correct date.

I am now looking at the second post by Lewiy and trying to figure out where exactly I should put that to make it work for me. Is it an either/or case or do I need to put it somewhere else. I think I have "excel block" though my knowledge is somewhat limited.

There is no issue with another column if needed.. i.e

Column 1... Date today
Column 2... Advance date for the Tuesday
Column 3... only comes into play if the date in column 2 exceeds 20

Thanks very much for the help so far..

The second formula takes everything into account based on the frequency of the resultant date being returned in the column above it. This modification should give you the correct week with a bit of luck:
Code:
``=IF(COUNTIF(\$B\$1:B1,IF(WEEKDAY(A2,2)=1,A2+29,A2+21+(2-WEEKDAY(A2,2))))<20,IF(WEEKDAY(A2,2)=1,A2+29,A2+21+(2-WEEKDAY(A2,2))),IF(WEEKDAY(A2,2)=1,A2+29,A2+21+(2-WEEKDAY(A2,2)))+3)``

For your first formula you could use the formula I posted above, i.e.

=D5+23-WEEKDAY(D5,2)

But assuming this formula is in E5 and copied down the column to calculate future dates for a series of dates in D5 down you could change it to the following to give you a date 3 days later if there were 20 or more of the original Tuesday date

=D5+23-WEEKDAY(D5,2)+(COUNTIF(E4:E\$4,D5+23-WEEKDAY(D5,2))>19)*3

Replies
7
Views
397
Replies
7
Views
803
Replies
5
Views
229
Replies
4
Views
984
Replies
6
Views
413

1,221,127
Messages
6,158,101
Members
451,464
Latest member
Holden3

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back