• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula for making a Gantt Chart

C

Cantonalives

Member
Hi All,
Irritated myself with what should be simple. Basically I am creating a Gantt chart based on start and end date. So if the Start date is greater the week commencing and the end date is less than or equal to the next week, a "Y" is the value.

Please see attached to see my jumble. Was using an AND formula, but the logic was not quite right.


Any thoughts, much appreciated.

Thanks.

-CL
 

Attachments

  • Book1.xlsx
    9.3 KB · Views: 3
Thanks but alas that does not work. So basically the the 'true' needs to start in the respective week commencing and end while in the respective week. commencing too.

So in the example G4 thru I4 would be 'true', but nothing else.
 
Ah, until it is just one date, so a milestone for instance.. In this example is shows 'y' in 2 different weeks. Please see attached.
 

Attachments

  • Book1 (2).xlsx
    9.4 KB · Views: 2
Nearly. If you look at the later file, yes it has 'Y' in 2 dates. However, Start and end date are the same, therefore the 'Y' should only be in one week.. It should not appear in Week Commencing 24/05/21.
 
vletm said:
Cantonalives
As I have written twice - I won't continue based one pair of dates data.
It would take weeks to test all [im/]possible variations.
Click to expand...


Apologies, I was missing your point there. Still not quite seeing what you mean. In my mind I am seeing and Start and an End data and then looking to ensure it falls within the correct week, be that 1 week or multiple weeks.

I guess this is exactly why I struggled with the logic. Thanks for trying.
 
Cantonalives
I have meant for a long time:
You have offered one pair of dates - did You get this?
after that I have offered something - did You get this?
You've offered new pair of dates, which don't work as You would like - did You get this?
This way - this would take weeks that You will one pair in time to test - did you get this?
You should able to give many pair of dates, which should cover as many cases You would have - did You get this?
... as well as ... You have there those two rows, with those You could modify formulas as those would work for You - did You get this?
... I guess that - You could modify those formulas Yourself too.
... or of course, soon someone would give exact formula for You - but then You have not learnt - how to do it Yourself?
 
Sorry, I get/got all the points you raised. The only way for me to test is to change the dates and try to adapt. It seems the only issue is that it does not work on a single date/week. So both dates being the same does not work. And here is one of the many logic issues I have come to in this process.

I would have thought the start and end date are almost irrelevant, as in - if formula is correct, they can be changed and still work.
 
Cantonalives
Do You mean that You cannot or won't test those dates / formulas Yourself?
If the result should be something else ... then You could modify formulas too ... and test again as long as it needs.
With those two lower rows, You could see Yourself when both rows are True.
 
You must log in or register to reply here.
Back
Top