mkohler28 Posted February 23, 2008 Share Posted February 23, 2008 I am trying to set up a calc field that shows a list of dates from a start date to and end date, but may or may not include Saturday, Sunday or Holidays unless a related value for those days or dates are present. I have field that generates a number (example number = 2) this number then calculates an end date based upon start date. 7/2/2008 WED [start date] 7/3/2008 THU 7/4/2008 FRI [end date] In my calc field I'm trying to evaluate and add or remove dates if any fall on a Sat, Sun or Holiday (I have three separate fields, one lists all Holiday dates, one is a Sat Number = 0 or 1 and a Sun Number = 0 or 1; Sat/Sun are both triggered by separate values) So in this example 7/4/2008 is a holiday, 7/5/2008 is Sat and 7/6/2008 is Sun and my separate field values are: Holiday Date = 1 Sat = 1 Sun = 1 ..so then I'm trying to get the calc field to then list the result as follows: 7/2/2008 WED [start date] 7/3/2008 THU 7/4/2008 FRI 7/5/2008 SAT 7/6/2008 SUN 7/7/2008 MON [end date] If Sat was = 0 then the result would be: 7/2/2008 WED [start date] 7/3/2008 THU 7/4/2008 FRI 7/5/2008 SAT [end date] I would appreciate any help on this... Link to comment Share on other sites More sharing options...
David Head Posted February 23, 2008 Share Posted February 23, 2008 I suspect that there are either some typos or information missing from this explanation. What does it mean when "Sat = 0"? I would have assumed that would mean to leave Saturdays out of the result. And I don't understand how Sat = 1 results in dropping a Sunday and a Monday. I am confused by the explanation. Link to comment Share on other sites More sharing options...
mkohler28 Posted February 23, 2008 Author Share Posted February 23, 2008 I guess I'll cut to the chase, to make this easier I am trying to determine a delivery date based upon pickup date + service days. I am trying to skip over Holidays and Weekends. In my sample below a user picks 2 day service on Weds 7/2, normally the delivery date would be Friday 7/4 but Friday is a holiday and of course I'm trying to avoid Satuday/Sunday & Holidays so the delivery date would be Monday. Some clients include Saturday or Sunday as a transit day so if that is true then I need to add that day and then in my sample below the delivery date would be Saturday...I hope this seems more clear, if you have a good way you would set this up my ears are wide open... Link to comment Share on other sites More sharing options...
David Head Posted February 23, 2008 Share Posted February 23, 2008 So why didn't you say that in the first place? I am not be specifically critical of you but I for one waste a lot of time trying to find solutions to the wrong problems. What you have presented is the wrong problem. You don't want a list of dates at all - you want a single delivery date based on pickup, service days and whether to include holidays and weekends. So the hint for anyone reading this - when asking questions, tell us what you want to do (what is the required result) and what you have tried. And try to give as much detail as to how you would do it without a computer too. So back to the problem. As I see it, you have defined Sat = 1 to mean "skip Saturdays". And the same with Sundays and Public Holidays. Is that correct? If so, I think you need better field names. For example: Skip_Saturday Skip_Sunday Skip_Holidays That will make it easier to see the logic of the calculation. Now first to get the weekends working. First read this KnowledgeBase article (3926) and see if that gets you started. Link to comment Share on other sites More sharing options...
mkohler28 Posted February 24, 2008 Author Share Posted February 24, 2008 Yeah, someone started me down the wrong path to begin with stating I should build a list of dates and compare them to a list of Holiday dates and Weekend days and I guess I was determined to make it work but it seem it was the wrong path. Thanks for your help and sorry for the round-about way. Link to comment Share on other sites More sharing options...
David Head Posted February 24, 2008 Share Posted February 24, 2008 No harm done smiley_cool Just remember that it does not matter how long you go down the wrong path, it is still wrong. So Cool! Link to comment Share on other sites More sharing options...
J Wenmeekers Posted February 24, 2008 Share Posted February 24, 2008 Dar sÃntomas errónéos, resulta en soluciónés erróneas. Link to comment Share on other sites More sharing options...
Jack Rodgers Posted February 24, 2008 Share Posted February 24, 2008 Just remember that it does not matter how long you go down the wrong path, it is still wrong. Wrong paths often have exit ramps onto the right path. If faced with 20 paths and the possibility that only one is the right path, how long must a person wait before choosing one? Especially if you do not have any information about right or wrong? Most of life is choosing a path with incomplete information hoping to adjust one's path if results tend to prove it is a bad choice. If, and only if, one can acquire enough information to make that choice will it matter which one you choose although the actual end result will matter but you have no way of knowing what that is. We should therefore not hold newbies, including ourselves, to impossible standards. It seems rather totalitarian to hold right/wrong standards up to people who don't even know east/west/north/south since they are just getting started. Link to comment Share on other sites More sharing options...
David Head Posted February 24, 2008 Share Posted February 24, 2008 Huh? smiley-undecided Where did that rant come from? All I said was that the wrong path is the wrong path. By extension, once you find it is wrong, get off. How does that make me totalitarian and holding everyone to impossible standards? I think you draw a very long bow and are inferring a lot from my comment. Link to comment Share on other sites More sharing options...
Jack Rodgers Posted February 24, 2008 Share Posted February 24, 2008 4 hot sauce packs on my Taco Bell? Link to comment Share on other sites More sharing options...
Jack Rodgers Posted February 24, 2008 Share Posted February 24, 2008 One suggestion: Create a date file and create one day for each calendar day from now until? Various calculated fields to cover your needs: These will resolve to a 1 C_ Saturday: dayname(date) = Saturday C_Sunday: etc. C_Holidayy: manual checked and so on as you discover needs. Now you can create as many multi-field-linked TOs as you need. Add a global field for each relationship: G_Friday G_Saturday G_Sunday G_Holiday Now link your two dbs with G_Saturday - C_Saturday G_Sunday - C_Sunday G_Holiday - C_holiday Now you have three linking fields. Put the globals on a layout and a portal showing the C_fields. Now when you check the checkbox only those days will show in the portal or not show depending upon how you design it. You can also design your calculated fields to show a one only for a date range which is even better for you. Now you just do a count for the records in the portal or find. Hope you get the idea, it is easier to set up than for me to describe. Link to comment Share on other sites More sharing options...
By Soliant Consulting,
By Soliant Consulting,
By Soliant Consulting,
Recommended Posts