Date Calculation

[Dibble]

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?

Thanks in advance

 

[Lewiy]

I think this formula will do what you want with regards to the Tuesdays where today’s date is in A1:

=IF(WEEKDAY(A1,2)=1,A1+22,A1+14+(2-WEEKDAY(A1,2)))

 

[Lewiy]

And this should take care of the Fridays:

=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
	A	B	C	D
1
2	25/07/2007	07/08/2007
3	25/07/2007	07/08/2007
4	25/07/2007	07/08/2007
5	25/07/2007	07/08/2007
6	25/07/2007	07/08/2007
7	25/07/2007	07/08/2007
8	25/07/2007	07/08/2007
9	25/07/2007	07/08/2007
10	25/07/2007	07/08/2007
11	25/07/2007	07/08/2007
12	25/07/2007	07/08/2007
13	25/07/2007	07/08/2007
14	25/07/2007	07/08/2007
15	25/07/2007	07/08/2007
16	25/07/2007	07/08/2007
17	25/07/2007	07/08/2007
18	25/07/2007	07/08/2007
19	25/07/2007	07/08/2007
20	25/07/2007	07/08/2007
21	25/07/2007	07/08/2007
22	25/07/2007	10/08/2007
23	25/07/2007	10/08/2007
24
25
Sheet1

 

[Dibble]

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?

 

[Dibble]

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

 

[barry houdini]

Try

=D5+23-WEEKDAY(D5,2)

 

[Lewiy]

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.

 

[Dibble]

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..

 

[Lewiy]

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:

=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)

 

[barry houdini]

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