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

 

[Dibble]

This is now starting to look good and the last coding from Barry is working nicely. Thanks.

As per most things when doing something like this somebody comes along and throws a spanner in the works!

At this time if the number of referrals to a Tuesday exceeds 20 then it provides a date on the Following Friday. It is unlikely that the Friday cases would ever exceed 20. However, if it did for whatever reason then there is currenly no safeguard to ensure the max of 20 per day is not exceeded. This date would then need to push to the following Tuesday.

There are also occasions where the formula would need to be overidden. For example. Referral today so far goes to the 14th August. But there are occasions where the case would be referred to an early date. This could easily be manually overwritten by the user. However it needs a safeguard to show that if they enter that date manually a search is done of the column and if the total number of referrals on that date exceeds 20 then an alternative date should be found.

Don't you just love your manager!!

 

[Dibble]

It seems the system as described is failing and the powers that be have decided to change the system.

Now if the date in C* is a Weds, Thurs, Fri, Sat, Sun or Mon then the date returned should be 2 weeks on Tuesday. So for today it would be 11th September.

If the C* date is a Tues then it should be a Tues three weeks so for tomorrow it would return 18th September.

The requirement to default to the Friday if the Tuesday limit of 20 is exceeded is still needed.

The current coding is by Lewiy

=IF(COUNTIF($B$1:D13,IF(WEEKDAY(C14,2)=1,C14+22,C14+21+(2-WEEKDAY(C14,2))))<20,IF(WEEKDAY(C14,2)=1,C14+22,C14+21+(2-WEEKDAY(C14,2))),IF(WEEKDAY(C14,2)=1,C14+22,C14+21+(2-WEEKDAY(C14,2)))+3)

I've changed and fiddled with this code and the numbers to try and get it to do what's needed but am failing. I can't see for looking now I think and I bet it's really simple.

I think the truth is although it works I really don't fully understand how this coding is working as it is beyond my capabilities.

Can anyone assist please?

 

[Dibble]

Cancel..

With a lot more messing and learning I appear to have solved it with this

=IF(COUNTIF($D$1:D4,IF(WEEKDAY(C5,2)=1,C5+15,C5+21+(2-WEEKDAY(C5,2))))<20,IF(WEEKDAY(C5,2)=1,C5+15,C5+21+(2-WEEKDAY(C5,2))),IF(WEEKDAY(C5,2)=1,C5+15,C5+21+(2-WEEKDAY(C5,2)))+3)

Thanks to Lewiy really as its his formula with a few twists by me as I learn.

 

[barry houdini]

Hello Dibble,

sorry I missed your previous post.....

based on my previous suggestion I think you could achieve this with

=C5+22+(COUNTIF($D$1:D4,C5+22-WEEKDAY(C5+5))>19)*3-WEEKDAY(C5+5)