10 Replies Latest reply on Mar 27, 2011 8:57 AM by FrankFarmer

    What week am I in??

    FrankFarmer

      Title

      What week am I in??

      Post

      Hi,

      I am new to FileMaker . I have searched and I cant find a solution to my problem.

      I am trying to find what week we are in in relatation to known dates.

      Fields I have.

      DATE START - user enters date

      DATE FINISH = START DATE + 56

      DAYS TILL FINISH = DATE FINISH - Get ( CurrentDate )

      IN WEEK = ??????

      What I am looing for is that during week one (day 1 - 7) the result for IN WEEK will be 1. Week two or days 8-14 the result will be 2. Days 49 - 56 Week 8 etc...

      Help Please...

      Thanks

      Frank

        • 1. Re: What week am I in??
          philmodjunk

          Ceiling ( (DATE FINISH - Get ( CurrentDate ) ) / 7 )

          • 2. Re: What week am I in??
            FrankFarmer

            Thank You!

            That calculation works perfectly to find the weeks but its not quite what I am looking for. The above calculation says, Take The FINISH DATE and minus today's date then divide that by 7. it returns the weeks passed since the start date.

            I need to have a return of "1" for days 1 - 7 and return "2" for days 8 - 14 so on and so on till week 8. I need the result so that I can look up what is needed in that week. I guess I could set a range but I would like to see it display the number of the week its in in relation to the start time.

            Thanks again for any help you can give!

            Frank

            • 3. Re: What week am I in??
              Frinholp

              Hi Frank

              Floor ( Abs ( Get ( CurrentDate )  -  DATE FINISH ) /7 )

              That should do it.

              Here you are subtracting your finish date from your current date - this will give you a negative value.

              The Abs function is the absolute value which is basically the positive equivalent e.g Abs ( -7 ) will become 7.

              The Ceiling ( number )  rounds up to the nearest integer, you will need Floor ( number ) which rounds down.

              Lee

              • 4. Re: What week am I in??
                FrankFarmer

                Hi Lee,

                Thanks for your response.

                When I input today's date as the start time the IN WEEK calculation returns 43.

                START DATE = 3/25/10 (today)

                FINISH DATE = 5/20/11    "START DATE + 56"

                TOTAL DAYS = 56     "DATE FINISH - START DATE" *Just a proof for me to know its right

                WEEKS TILL FINISH = 8      "Ceiling ( (DATE FINISH - Get ( CurrentDate ) ) / 7 )"

                IN WEEK = 43     "Floor ( Abs ( Get ( CurrentDate )  -  DATE FINISH ) /7 )"

                It should be  IN WEEK = 1   then not click over to IN WEEK = 2 until 4/2/11

                Its also not right when I enter other start dates.

                Thanks again for your input.

                Frank

                • 5. Re: What week am I in??
                  Frinholp

                  Sorry misread your question.

                  I would like to see it display the number of the week its in in relation to the start time

                  Should you not be using your start date?

                  The formula below will count how many weeks it is from your start date. If the start date is today it will evaluate as week 1. Bear in mind, if you subtract two exact dates you will get a result of 0.

                  Abs ( Floor ( ( ( STARTDATE -1 ) - Get ( CurrentDate ) ) / 7 ) )

                  Obviously it will run to the 9th week though. If you are storing the value, maybe you want to say

                  If ( Abs ( Floor ( ( ( STARTDATE -1 ) - Get ( CurrentDate ) ) / 7 ) ) < 9 ; Abs ( Floor ( ( ( STARTDATE -1 ) - Get ( CurrentDate ) ) / 7 ) ) ; "Elapsed")

                  Lee

                  • 6. Re: What week am I in??
                    FrankFarmer

                    Hi Again,

                    Im still not getting the right output. I am working on learning how  to write this stuff and I cant wrap my head around it. Im trying to get a  handle on nested IF statements. Is that how I should do this? I cant  find a caculation to do what i want it to.

                    "In Week" is a number field.

                    IN WEEK =

                    START DATE – FINISH DATE = XDAYS

                    IF XDAYS = 1 – 7 THEN RETURN “1”

                    IF XDAYS = 8 – 14, THEN RETURN “2”

                    IF XDAYS = 15 – 21, THEN RETURN “3”

                    IF XDAYS = 16 – 22, THEN RETURN “4”

                    IF XDAYS = 23 – 29, THEN RETURN “5”

                    IF XDAYS = 30 – 36, THEN RETURN “6”

                    IF XDAYS = 37 – 43, THEN RETURN “7”

                    IF XDAYS = 44 – 50, THEN RETURN “8”

                    IF XDAYS = 51 - 56, THEN RETURN “8”

                    IF XDAYS = 56 – INFINITE, THEN RETURN “FINISHED”

                    Thanks so much for the input so far. I am having fun working on this.  This project will help a lot of people and when its finnished I will be  giving it away for free.

                    Frank

                    • 7. Re: What week am I in??
                      LaRetta_1

                      Try:

                      Let (
                      xdays = Case ( FINISH DATE ; FINISH DATE - START DATE ) 
                      ;
                      Ceiling ( xdays / 7 )
                      )

                      • 8. Re: What week am I in??
                        LaRetta_1

                        UPDATE: I had just woke up and didn't read this part "IF XDAYS = 56 – INFINITE, THEN RETURN “FINISHED”

                        Your request doesn't quite make sense.  If there is a finish date then the project *is* finished and it should not keep evaluating to larger numbers past the finish date at all.  If there is no finish date then it should say, if anything, "NOT FINISHED".  But what if it is not finished and 8 weeks or less?

                        I am afraid that you have confused me along with everyone else on your requirements.  :^)

                        Also, weeks 4 and 8 aren't logical.

                        • 9. Re: What week am I in??
                          LaRetta_1

                          Let's clarify the rules (and please correct any of these rules):

                          1) If there is a finish date, the calculation should use that number of weeks between start date and finish date and then stop calculating.
                          2) If there is not a finish date and today's date is within the 8-week period, it should produce that week number.
                          3) If there is no finish date and today's date is GREATER than the 8-week period, display "FINISHED".  I don't know why you just don't want it to display 8 as the final max week number.

                          Calculation result must be text to allow for "FINISHED" and set storage options to unstored:

                          Let ( [
                          finish = Case ( FINISH DATE ; FINISH DATE ; Get ( CurrentDate ) ) ;
                          xdays =  finish - START DATE ;
                          wks = Ceiling ( xdays / 7 )
                          ] ;
                          Case ( wks  ≤ 8 ; wks ; "FINISHED" )
                          )

                          I do not know what to do with the illogical sequence in your above example.  I will assume that it was a simple typo. Laughing

                          • 10. Re: What week am I in??
                            FrankFarmer

                            Hi All,

                            I figured it out using if statements. I was up almost all night learning about the biggest little word in the English language. I want to thank every one who helped on this challenge for. I have learned a bunch from you in the last few days.

                            My Solution

                            If (Get ( CurrentDate ) - START DATE <= 7; "Week 1";
                            If (Get ( CurrentDate ) - START DATE <= 15; "Week 2";
                            If (Get ( CurrentDate ) - START DATE <= 22; "Week 3";
                            If (Get ( CurrentDate ) - START DATE <= 29; "Week 4";
                            If (Get ( CurrentDate ) - START DATE <= 36; "Week 5";
                            If (Get ( CurrentDate ) - START DATE <= 43; "Week 6";
                            If (Get ( CurrentDate ) - START DATE <= 50; "Week 7";
                            If (Get ( CurrentDate ) - START DATE <= 56; "Week 8";
                            If (Get ( CurrentDate ) - START DATE >= 57; "FINISHED";
                            IN WEEK)))))))))

                            Again Thank You!!!!!

                            Frank