How many days, months and years

[Raybaudi]

Hi all

I know how calculate Age.

But I can't calculate how many days, months and years a worker worked, given a startDate and an EndDate (may be I have a black hole in my mind)

So if the worker worked:

from 01/01/2001 to 01/01/2001 he had worked 1 day

from 01/01/2001 to 31/12/2001 he had worked 1 year

from 01/01/2001 to 01/01/2002 he had worked 1 year and 1 day

and so on...

Can someone give me an idea ?

Edited November 28, 2005 by Guest

[Lee Smith]

Hi Danielle,

I'm not sure I'm reading this right, but if you are looking to include conclusive dates, wouldn't you just need to add +1 to your start date?

Lee

[Raybaudi]

Hi Lee

yes, I thinked the same, but no one of my age calcs works right... and i'm working on this calc from about 3 days with no correct result !

[Lee Smith]

Hi Daniele,

Between the two of us, I'm sure you have used all of the one's I would have come up with then.

Off hand, can't you just add 1 to the results?

GetAsNumber(Year(EndDate) -

Year(StartDate) - Case(EndDate <

Date(Month(StartDate); Day(StartDate); Year(EndDate)); 1; 0)) & " Years, "

&

GetAsNumber(Mod(Month(EndDate) -

Month(StartDate) + 12 - Case(Day(EndDate) <

Day(StartDate); 1; 0); 12)) & " Months, "

&

GetAsNumber(Day(EndDate) -

Day(StartDate) + Case(Day(EndDate) >=

Day(StartDate); 0; Day(EndDate -

Day(EndDate)) <

Day(StartDate); Day(StartDate); Day(EndDate -

Day(EndDate))))+1 & " Days"

[Raybaudi]

GetAsNumber(Day(EndDate) -

Day(StartDate) + Case(Day(EndDate) >=

Day(StartDate); 0; Day(EndDate -

Day(EndDate)) <

Day(StartDate); Day(StartDate); Day(EndDate -

Day(EndDate))))[color:red]+1 & " Days"

No, i think.

For example, if i worked

from 01/01/2001 to 31/12/2001

that was 1 year, but your calc will give:

0 Years, 11 Months, 31 Days

And it must be so simple !

[comment]

Search the forum - I believe we have done this one to death...

[Raybaudi]

Hi comment,

searched...

with "working days" there are some match, but only for those days of week w/o saturday and sunday.

with "age" i can only find calcs for age...

BTW i was thinking that it is a simple tiny thing...

[SlimJim]

There is a CF here:

http://www.briandunning.com/cf/57

which I think does what you are asking for.

[Lee Smith]

hi Daniele,

I searched also, and didn't find any thing but the Birthday Calculations.

I modified my earlier post and it seems to work.

GetAsNumber(Year(EndDate) -

Year((StartDate)-1) - Case(EndDate <

Date(Month((StartDate)-1); Day((StartDate)-1); Year(EndDate)); 1; 0)) & " Years, "

&

GetAsNumber(Mod(Month(EndDate) -

Month((StartDate)-1) + 12 - Case(Day(EndDate) <

Day((StartDate)-1); 1; 0); 12)) & " Months, "

&

GetAsNumber(Day(EndDate) -

Day((StartDate)-1) + Case(Day(EndDate) >=

Day((StartDate)-1); 0; Day(EndDate -

Day(EndDate)) <

Day((StartDate)-1); Day((StartDate)-1); Day(EndDate -

Day(EndDate)))) & " Days"

[Lee Smith]

You will still need to modify it too

Let ( leapFactor = If ( Mod ( Year ( EndDate ) ; 4 ) = 0 ; 1 ; 0 );

Case (

// Age in years

Year ( EndDate ) - Year ( (StartDate)-1 ) - ( ( DayOfYear ( EndDate ) - leapFactor ) < DayOfYear ( (StartDate)-1 ) );

// Age in years and days

Year ( EndDate ) - Year ( (StartDate)-1 ) - ( ( DayOfYear ( EndDate ) - leapFactor ) < DayOfYear ( (StartDate)-1 ) ) & " years and " & Case (

( DayOfYear ( EndDate ) - leapFactor ) ≥ DayOfYear ( (StartDate)-1 ) ; DayOfYear ( EndDate ) - leapFactor - DayOfYear ( (StartDate)-1 ) ;

DayOfYear ( EndDate ) + ( DayOfYear ( Date ( 12 ; 31 ; Year ( EndDate ) ) - DayOfYear ( (StartDate)-1 ) - leapFactor ) ) ) & " days" ;

// Age in years, months and days

Year ( EndDate ) - Year ( (StartDate)-1 ) - ( ( DayOfYear ( EndDate ) - leapFactor ) < DayOfYear ( (StartDate)-1 ) ) & " years and " & Mod ( Month ( EndDate ) - Month ( (StartDate)-1 ) + 12 - (Day ( EndDate ) < Day ( (StartDate)-1 ) ) ; 12 ) & " months, and " & (EndDate - Date ( Month ( EndDate ) - (Day ( EndDate ) < Day ( (StartDate)-1 ) ) ; Day ( (StartDate)-1 ) ; Year ( EndDate ) ) ) & " days"

)

)

[Raybaudi]

Lee, thank you very much !

You do exactly what i made (sure wronger) and now what didn't work to me, work with your calc...

perhaps i made some mistakes with all those "-1" :)

[Raybaudi]

Your first calc is good...

the second one, that is on Brian site, isn't.

try from 29/02/2000 to 28/02/2004

the first gives (exactly) 4 Years, 0 Months, 0 Days

the second gives 3 years and 0 months, and 0 days

[Raybaudi]

But...(I'm asking myself)

it's possible that there isn't a better (to look) solution ?

May be with Jason's matrix ?

[Lee Smith]

Actually, it gives 3 years and 365 days, which would be 4 years in non leap year years. ??

Leap year, more specifically the month of Feb will mess up your calculations. It usually comes down to what you want the answer to be.

Funny you should mention Jason, because that is the person I would have asked this question. LOL

Lee

[Raybaudi]

No, the second gives incorrect results...

or can't handle leap years or leap years must no enter in this type of calc !

Try also:

from 29/02/2000 to 29/02/2004

from 28/02/2000 to 27/02/2004

and (the best)

from 28/02/2000 to 01/02/2004

(3 years and 339 days)

[comment]

See here:

http://fmforums.com/forum/showtopic.php?tid/166684

[Lee Smith]

That was my bad, I missed it up when converting it.

I have created a CF for the formula that is in the FileMaker Knowledge Base that gbdoc point to:

It is interesting how the number of days dwindle when you only put todays date in it, and it calculates from 1/1/0001. Off about 1 month and 15 days

I guess if you are over 2005 years old, it wouldn't matter if they make you younger though. ROTFLMAO

Lee :hair:

Edited November 28, 2005 by Guest
correct spelling

[Raybaudi]

Hi comment

i was aware of that topic !

But still i can't understand why the age calc works and the worked days ( with a near calc) no.

There are inconsistent result also in the first calc of Lee... ( and I don't know why )

from 01/01/2000 to 31/01/2000 1 month

from 01/03/2002 to 31/03/2002 1 month and 3 days

from 01/05/2004 to 31/05/2004 1 month and 1 day

I'm considering to solve with matrix, as this problem is only the first part... the second beeing add and subtract those quantities !

Real, they are incompatibil to add, like apples and oranges, but we can always say:

(2 apples and 1 orange) + (3 apples and 2 oranges) = 5 apples and 3 oranges !

[Raybaudi]

Hi Lee

give me that CF :)

[comment]

The problem is not solving, but what to solve. Objectively, there is no correct answer to your question. You have to make some arbitrary assumptions, for example is Mar 31 to April 30 a full month? If yes, is Mar 30 to April 30 a full month? If not, then how can Mar 1 to April 1 be a full month?

If you can establish the rules that the calc should follow, then it can be solved. Other people might make other rules, and then your calc will give them "inconsistent results".

[Lee Smith]

Here you go.

Requires two date fields and a calculation with the result of Text:

    [color:blue] for Inclusive Dates, modify the modify the Let by adding +1 to the

    [color:red]Number_of_Days = Current_Date - StartDate +1;

//Calculating Elapsed Time Between Two Dates Broken Into Year, Month, And Day

StartDate            (could be a Date of Birth, etc.)

Current_Date      (could be a manual Date, or the Get( Current Date)

Let(

Number_of_Days = Current_Date - StartDate;

Let(

Month_Length =Case(Month(StartDate) = "1"; "31"; Month(StartDate) = "2" ; "28" ; Month(StartDate) = "3" ; "31" ; Month(StartDate) = "4" ; "30" ; Month(StartDate) = "5" ; "31" ; Month(StartDate) = "6" ; "30" ; Month(StartDate) = "7" ; "31" ; Month(StartDate) = "8" ; "31" ; Month(StartDate) = "9" ; "30" ; Month(StartDate) = "10" ; "31" ; Month(StartDate) = "11" ; "30" ; Month(StartDate) = "12" ; "31" );

(Int( Number_of_Days / 365.25 )) & " Years " & If((Round( Mod( Number_of_Days ; 30.4375 ) ; 0 )) <= Month_Length and ((Int(Number_of_Days / 30.4375))) < "1" ; "0" ; ((Int(Number_of_Days / 30.4375) - (12 * (Int( Number_of_Days / 365.25 ))))))

& " Months " & (Round( Mod( Number_of_Days ; 30.4375 ) ; 0 )) & " Days")

)

Let me know if you think it is worth posting to Brian's site.

Lee

[Raybaudi]

Ok Lee

This must be the calc of your CF:

Let([

Number_of_Days = Date2 - Date1 + 1;

Month_Length = Case(Month(Date1) = "1"; "31"; Month(Date1) = "2" ; "28" ; Month(Date1) = "3" ; "31" ; Month(Date1) = "4" ; "30" ; Month(Date1) = "5" ; "31" ; Month(Date1) = "6" ; "30" ; Month(Date1) = "7" ; "31" ; Month(Date1) = "8" ; "31" ; Month(Date1) = "9" ; "30" ; Month(Date1) = "10" ; "31" ; Month(Date1) = "11" ; "30" ; Month(Date1) = "12" ; "31" )

];

(Int( Number_of_Days / 365,25 )) & " Years " & If((Round( Mod( Number_of_Days ; 30,4375 ) ; 0 )) <= Month_Length and ((Int(Number_of_Days / 30,4375))) < "1" ; "0" ; ((Int(Number_of_Days / 30,4375) - (12 * (Int( Number_of_Days / 365,25 ))))))

& " Months " & (Round( Mod( Number_of_Days ; 30,4375 ) ; 0 )) & " Days"

)

But it seemed to me that don't work !

[Raybaudi]

for example is Mar 31 to April 30 a full month?

From my point of view it is: 1 month and 1 day [color:red](all April month + 1 day of March)

If yes, is Mar 30 to April 30 a full month?

From my point of view it is: 1 month and 2 day [color:red](all April month + 2 days of March)

If not, then how can Mar 1 to April 1 be a full month??

From my point of view it is: 1 month and 1 day [color:red](all March month + 1 day of April)

The rule is simple:

1)how many days of a mounth i worked ?

from 15/03/2000 to 14/04/2000 I worked 17+14=31 days or 1 month and 1 day (assuming a month of 30 days when there isn't a complete mounth)

[Lee Smith]

If you are using as a CF, what I posted is all you need.

If you are going to use it as a calculation, you need to create the fields that are explained in the Tech Notes Here

and If I'm not understanding, here is a sample file using both:..

HTH

Lee

Elapsed_to_Text.fp7.zip

[comment]

Well, if you can work out when a period starting on Day N of Month M, and ending on Day N of Month M + 1 becomes 1 month and 2 days (your answer to N = 30) instead of 1 month and 1 day (your answer to N = 1), then you can write your calc.

I am not getting into this. The operative words here are "from my point of view".

[Raybaudi]

thank you Lee

i have to make some adjustment to your calc to suit "my point of view", but the idea to start from is good ! :)

[comment]

Hi Lee,

Since your formula calculates the number of AVERAGE years and AVERAGE months elapsed between two dates (as does the one on FMI's site), you can make it simply:

Let ( [

n = Current_Date - StartDate;

y = Div ( n ; 365.25 ) ;

r = Mod ( n ; 365.25 ) ;

m = Div ( r ; 30.4375 ) ;

d = Round ( Mod ( r ; 30.4375 ) ; 0 )

] ;

y & " Years " & m & " Months " & d & " Days "

)

[Raybaudi]

Hi comment

you are always "the best" ! :

Edited November 30, 2005 by Guest
a big my mistake !

[Raybaudi]

I am not getting into this. The operative words here are "from my point of view".

Hi comment

even if you said that, I think that you can give me an help !

I'm considering to make a recursive custom function for this calc...and I know that you love (as I do) those CF !

This is my starting point:

Idea1(StartDate;EndDate)

________________________________________________

Let([

Month_Length =Case(Month(StartDate) = "1"; "31"; Month(StartDate) = "2" ; 28 + If ( Mod ( Year ( StartDate ) ; 4 ) = 0 ; 1 ; 0 ) ; Month(StartDate) = "3" ; "31" ; Month(StartDate) = "4" ; "30" ; Month(StartDate) = "5" ; "31" ; Month(StartDate) = "6" ; "30" ; Month(StartDate) = "7" ; "31" ; Month(StartDate) = "8" ; "31" ; Month(StartDate) = "9" ; "30" ; Month(StartDate) = "10" ; "31" ; Month(StartDate) = "11" ; "30" ; Month(StartDate) = "12" ; "31" );

EndOfMonth = Date ( Month(startDate) ; Month_Length ; Year(startDate) )

];

Case(

EndDate < StartDate;"";

Day ( StartDate ) = 1 and EndDate = EndOfMonth; 1/100 ;

Day ( StartDate ) ≥ 1 and EndDate ≤ EndOfMonth; (EndDate - StartDate + 1) /10000;

Day ( StartDate ) = 1 and EndDate > EndOfMonth; 1/100 + Idea1(EndOfMonth +1 ;EndDate);

Day ( StartDate ) > 1 and EndDate > EndOfMonth; (EndOfMonth - StartDate + 1) /10000 + Idea1(EndOfMonth +1 ;EndDate);

""

)

)

________________________________________________

this CF works, but I have to make days more that 30 to 1 month + days and months more than 12 to 1 year + months !

And sure you know how to optimize the CF ! :

For now the result is of the floating type, so for 11 months and 28 days it is 0,1128

And all those conditions are respected!

________________________________________________

comment Said:for example is Mar 31 to April 30 a full month?

From my point of view it is: 1 month and 1 day (all April month + 1 day of March)

comment Said:If yes, is Mar 30 to April 30 a full month?

From my point of view it is: 1 month and 2 day (all April month + 2 days of March)

comment Said:If not, then how can Mar 1 to April 1 be a full month??

From my point of view it is: 1 month and 1 day (all March month + 1 day of April)

________________________________________________

[comment]

I cannot help you when I don't know what you are trying to do. There have been many formulas in this thread, but no clarification regarding the purpose.

You have provided answers that your calc should return in some specific examples - but I don't see a general rule.

---

BTW, I don't understand your correction to my formula. Out of n days, each group of 356.25 days makes an average year. Out of the REMAINING r days, each group of 30.4375 days make an average month. If you go back to n, you are counting the same days twice, so instead of '1 Year, 6 Months and 15 Days' you will get '1 Year, 18 Months and 15 Days'. Out of your 18 months, 12 are already included in the year.

[Raybaudi]

There have been many formulas in this thread, but no clarification regarding the purpose.

the purpose of this calc is to have the nearest to the true amount of time that a worker worked in his life for pensionistic purpose, because in Italy these are valued in Years, Months and Days.

But so to speak..., i think that my "point of view" isn't only MINE and a calc of this type can be of general interest.

I go to work today 30/11/2005 from 8:00am to 6:00pm... and the same time i'll work tomorrow.

I will have worked 2 days. But if my last day of work will be 31/12/2005, i perfecty can said that i will have worked 33 days or 1 month and 2 days.

During a worker life, he can have many workplace starting and ending in various date. And he can have an interruction of work too, while he was working for someone. So, to make an example:

first job from 10/01/1990 to 29/12/2000

second job: from 01/01/2001 to today, but during this, he stopped from 10/08/2002 to 25/12/2002.

first job was: 10 years, 11 months and 21 days

second job is: 4 years, 6 months and 16 days

so today he is matured 15 years, 6 months and 7 days (assuming 30 days = 1 month)

Better than so, i can't expain me.

BTW for your calc I simple :

[comment]

Quote

nearest to the true amount of time that a worker worked in his life for pensionistic purpose, because in Italy these are valued in Years, Months and Days.

It sounds like a legal matter. I would check the exact wording in the defining document, because (as I have said again and again) "years, months and days" is meaningless by itself.

If you don't have that, I would suggest that you first decide whether you want to calculate the AVERAGE or the ACTUAL, then stick with your choice. Mixing the two does not seem a good choice to me.

To divide the elapsed period into AVERAGE years, AVERAGE months and remaining days, use my formula above.

To calculate the ACTUAL elapsed years, months and days, I think that the most sensible (or the least illogical) approach would be this one. It's not the best optimized formula, but the basic method is sound: it does the usual age calculation for years, a similar calculation for the months, and counts the actual remaining days in the starting and ending month.

---

One more thought:

"ACTUAL" means "in context". 28 days, in the context of February 2006, are 1 month. 28 days, in the context of January, are 28 days.

If at the end of your calculation you need to combine two or more different periods, such as your example of:

first job was: 10 years, 11 months and 21 days

second job is: 4 years, 6 months and 16 days

you are by necessity dealing with data out of its original context. So you are forced to make assumptions (such as your assumption that a month is 30 days). If you are going to make such assumption, why try so hard to make an actual calculation in the first place (which is also based on assumptions of its own)?