Date calculations

Questions and Answers about DataEase for Windows

Date calculations

Postby Pete Stobbs » Thu Jan 27, 2011 9:06 am

Hi all,

I have 2 fields, Date01 and Date02.

How do I:

Get the number of days between the two dates.

Get the number of weeks between the two dates.

Get the number of months between the two dates.

Get the number of years between the two dates.

Cheers.

Pete.
Pete Stobbs
 
Posts: 39
Joined: Thu Jan 24, 2008 8:03 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: Date calculations

Postby Adrian Jones » Thu Jan 27, 2011 11:53 am

For days:

Date1 - Date2

if 1 is going to be bigger than 2, or

abs ( Date1 - Date2 )

if you simply want to know 'the gap'.

Weeks = above / 7
Years = above / 365.25 for a figure that works 100% accurately in 99.999% of cases (cos of leap years & leap centuries)
Months = above / 12 if you are ok with an approximate figure cos of different lengths of months.

Round, floor etc according to need.
User avatar
Adrian Jones
 
Posts: 2000
Joined: Tue Sep 11, 2007 2:38 pm
Location: Cornwall, UK
Has thanked: 5 times
Been thanked: 4 times
 

Re: Date calculations

Postby KjetilUrne » Thu Jan 27, 2011 5:27 pm

Pete, I do have some experience with apps where 99.999% accuracy isn't good enough. If this is your case, consider building supporting calendar tables. They could hold info like incrementals on dates, weeknumbers, alternative abbrevations, ... (I've even used it for calculations that are broken down to an hourly level, managing exceptions like daylight saving time adjustments).

Excel is great for creating the basic data, then dumping it all to Ff/Dfw
KjetilUrne
 
Posts: 602
Joined: Mon Oct 08, 2007 8:21 am
Location: Norway
Has thanked: 4 times
Been thanked: 1 time
 

Re: Date calculations

Postby Pete Stobbs » Thu Jan 27, 2011 8:54 pm

Thanks guys,

Adrian,

Using Date01 = 01/03/2011

Using Date02 = 01/01/2011

In the NoOfMonths field with the derivation (TheDate01 - TheDate02) / 12

it gives 4 as the answer!

Pete
Pete Stobbs
 
Posts: 39
Joined: Thu Jan 24, 2008 8:03 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: Date calculations

Postby Adrian Jones » Fri Jan 28, 2011 7:41 am

Sorry, Pete, being a bit daft.

365.25 = avg number of days in a year (at least from 1901 -- 2099).
12 = number of months in a year.
So you need to divide by ( 365.25 / 12 ) for the average # of days in a month.

Are these approximate values ok for your needs?
User avatar
Adrian Jones
 
Posts: 2000
Joined: Tue Sep 11, 2007 2:38 pm
Location: Cornwall, UK
Has thanked: 5 times
Been thanked: 4 times
 

Re: Date calculations

Postby Pete Stobbs » Sun Jan 30, 2011 7:32 pm

Adrian,

That does nicely!!

Thanks

Pete
Pete Stobbs
 
Posts: 39
Joined: Thu Jan 24, 2008 8:03 pm
Has thanked: 0 time
Been thanked: 0 time
 
 

Return to DataEase for Windows

Who is online

Users browsing this forum: No registered users and 3 guests

cron