Ceiling ( (DATE FINISH - Get ( CurrentDate ) ) / 7 )
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!
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.
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.
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")
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.
xdays = Case ( FINISH DATE ; FINISH DATE - START DATE )
Ceiling ( xdays / 7 )
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.
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 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.
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";
Again Thank You!!!!!