I have two fields - "toggle", which is a global, and "duration". Toggle has a value of either 1 or 0, entered by a script. Duration is a calculation field using the Case function to determine it's contents (result is text): 

Case(
toggle = 0 ; enddate - startdate & " days" ;
toggle = 1 ; [insert week and day calculation here]
)

thing is, if the duration result in days is less than 7, then weeks aren't displayed (basically 0 or 1 would return the same result).

 

How do I turn days into week and days?

How do I turn days into week and days?

 

weeks = Div ( enddate - startdate ; 7 )

days = Mod ( enddate - startdate ; 7 )

 

Know thy Mod() function, when thou cometh to date calculations.

LOL, that's true (and strangely familiar...). But this is not really a date calculation as such - just your standard algorithm for dividing things into groups, like:

 

packs = Div ( beers ; 6 )

cans = Mod ( beers ; 6 )

But this is not really a date calculation as such - just your standard algorithm for dividing things into groups

  

Indeed, as dates are numbers, they fit here as well and your first calculation...

 

weeks = Div ( enddate - startdate ; 7 )

days = Mod ( enddate - startdate ; 7 )

 

… displayed that correlation quite nicely … just as this is a number and a date calc; it doesn't matter whether using one date (producing total number of days) or subtracting one date from another:  Date - Mod ( Date - 1 ; 7 )

 

STOP!  YOU'RE BOTH RIGHT!  It's a floor wax AND a dessert topping!  (Saturday Night Live about 20 years ago)

Thanks comment and LaRetta.

 

Here is the calculation I came up with which works quite well: 

If(not IsEmpty(start_date);If(not IsEmpty(end_date) ;

Case (toggle = 0 ; end_date - start_date + 1 & " days" ; 

end_date - start_date  ≤ 6 ;  end_date - start_date + 1 & " days" ;

toggle = 1 ; Div(end_date - start_date + 1; 7) & "w " & Mod(end_date - start_date + 1; 7) & "d"))

; 

""
)

I would like when toggle = 1 to also display months and years, IF the specified range qualifies for that extra info. Is this possible without completely changing the formula?

Say end_date - start_date + 1 = 800. How many years and months is that?

Say end_date - start_date + 1 = 800. How many years and months is that?

 

about 2 years, 2 months or so?

Yes, "about" being the operative word here. So do you want an "about" result or an accurate one? Because I don't know how to calculate an accurate answer in a meaningful way. Perhaps I should have picked a better number of days to demonstrate, say 393 (= 365 + 28). This could be one year and 28 days, or one year and one month - if that month is deemed to be a February. And if that year and the one that follows is not a leap year. In short, it could be anything you want it to be, and no one could say if it's wrong or right.

 

See also:

http://fmforums.com/forum/topic/35129-the-old-question-how-old-are-you/#166888

Yes, "about" being the operative word here. So do you want an "about" result or an accurate one? Because I don't know how to calculate an accurate answer in a meaningful way. Perhaps I should have picked a better number of days to demonstrate, say 393 (= 365 + 28). This could be one year and 28 days, or one year and one month - if that month is deemed to be a February. And if that year and the one that follows is not a leap year. In short, it could be anything you want it to be, and no one could say if it's wrong or right.

 

See also:

http://fmforums.com/forum/topic/35129-the-old-question-how-old-are-you/#166888

 

I see. I didn't realize the full extent of this, as you've explained. I suppose a rough estimate would suffice, but in the end I actually doubt it would be needed. Perhaps just months, weeks and days.

 

I get into a habit sometimes of thinking "how else can I display this data?" or assume a different situation and solutions the user may be presented with.

I suppose a rough estimate would suffice

 

See if this helps:

http://fmforums.com/forum/topic/89899-age-calculation/#entry412505