Date Calculation

Dibble

New Member
Joined
Dec 21, 2003
Messages
25
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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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)))
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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
 

Dibble

New Member
Joined
Dec 21, 2003
Messages
25
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

New Member
Joined
Dec 21, 2003
Messages
25
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
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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

New Member
Joined
Dec 21, 2003
Messages
25
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

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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)
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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
 

Forum statistics

Threads
1,181,055
Messages
5,927,862
Members
436,573
Latest member
CMR237

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top