RE: Date Calculation

Hi

I have two date fields and I need to find out the difference between
the two in order to find out the duration.

For example DateB-DateA = No of days.  I have set the field to display
a calculated result and it is specified as a number field.

I have got this working before but it doesn't seem to want to work
now.  Is there something obvious I am missing?  I think it might be to
do with the format of the date fields; maybe they should be number - I
just need a point in the right direction :-)

Also while I am on the subject I need the date fields to be displayed
in a user friendly format such as 01/01/08 but they need to be stored/
exported as 20080101, is there any way of doing this?

Many Thanx

> I have got this working before but it doesn't seem to want to work
> now.

What is it doing now? Subtracting one date from another returns the
number days between them. What are you experiencing?

> Also while I am on the subject I need the date fields to be displayed
> in a user friendly format such as 01/01/08 but they need to be stored/
> exported as 20080101, is there any way of doing this?

Its usually simplest to create a calculated field which contains the
exact formatting you want for the export.

cheers,

Hey Thanx D-42.

Actually it's wierd it just displays a really long number such as
1783215.  I don't have the file in front of me at the moment.

Tahnks in Advance

Hi
In FileMaker, dates are (inside) represented by the number of days since 
1/1/0001 (I think, if not it is since 1/1/0004). BTW, hours are represented 
in number of seconds.
Ie today is > 700000.
To play with dates always remember that and manage to set the format as 
appropriate.
I personally - in order not to think... - I always duplicate 'MyDate' (Date) 
by another calc field 'MyDateN' (number) = 'MyDate'. Then if I want a 'Find' 
of a range of dates, I simply create first a SetField(gn1; BegDate &
"..." 
EndDate) -where 'gn1' is a global(number) or a variable -, then I do the 
Find :
Mode Find
Set Field( MyDateN; gn1)
Perform Find
If (error current = 401)
etc..  (pseudo code)
I know that most people actually do that with a more elegant method 
(avoiding the extra number fields).
Just my 2 cents.
Remi-Noel


"JayBee" <Jennifer_Beecroft@hotmail.com> a écrit dans le message
de 
news:0843bad5-5020-4977-a62b-beccfee1010e@e10g2000prf.googlegroups.com...
> Hey Thanx D-42.
>
> Actually it's wierd it just displays a really long number such as
> 1783215.  I don't have the file in front of me at the moment.
>
> Tahnks in Advance
> Jenny 

On Mar 17, 2:49 pm, JayBee <Jennifer_Beecr...@hotmail.com> wrote:
> Hey Thanx D-42.
>
> Actually it's wierd it just displays a really long number such as
> 1783215.  I don't have the file in front of me at the moment.

As Remi-Noel indicated dates are stored internally as an integer
offset from 1/1/0001, so if you evaluate a modern date in a number
field you end up with large numbers.

However, subtracting two modern dates should give you a small number.
ie "Mar 13, 2008" - "Mar 11, 2008"  = 2

If you are getting a large number from this subtraction, there is
probably either an issue with the format of one or both of the dates.
e.g. the first is a date, and converted to a large int, but the
second, for example, might be treated as text, and filemakers method
of converting text to numbers is logical but little obtuse; and in any
case will probably result in a fairly small number. Which would
explain why your result is way off.

Remi-Noels's suggestion of creating surrogate number fields isn't ever
necessary but its a really good idea to help you diagnose and resolve
the issue you are having.

cheers,