Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello:
I need to get the time work by an employee. In years, months, and days.
having a start date and end date.
use the following code and it did not work.
examplo:
end date:18/04/2014
start date: 15/06/2010
result:
years:3
months:10
days:3
IF
([End Date] <= AddYears([Start Date],1),'0 Years ',
Age([End Date],[Start Date]) & ' Years ')
&
IF(MONTH([End Date]) = MONTH([Start Date]),'0 Months ',
IF(DAY([Start Date]) < DAY([End Date]), fabs(MONTH([End Date])-Month([Start Date])) & ' Months ',
((MONTH([End Date])+12)-Month([Start Date]))-1 & ' Months '
))
&
IF(MONTH([End Date]) = MONTH([Start Date]),DAY([End Date]) - DAY([Start Date]) & ' Days',
IF(DAY([End Date]) > DAY([Start Date]),DAY([End Date]) - DAY([Start Date]) & ' Days',
DAY(DATE(Interval([End Date] - [Start Date])))-1 & ' Days'
))
helpme
please
rolando
Maybe like this:
Set DateFormat = 'DD/MM/YYYY';
LOAD *,
Age([End Date],[Start Date]) & ' Years '
&
IF(month([Start Date]) < month([End Date]),
mod((month([End Date]) - month([Start Date])) + (Day([Start Date]) > Day([End Date])),12) & ' Months ',
mod(12-(month([Start Date]) - month([End Date])) + (Day([Start Date]) > Day([End Date])),12) & ' Months ' )
&
if(Day([End Date])> Day([Start Date]), Day([End Date])-Day([Start Date]) & ' Days', day(monthend([Start Date]))-day([Start Date])+day([End Date]) & ' Days') as Diff;
LOAD * INLINE [
End Date, Start Date
18/04/2014, 15/06/2010
18/06/2014, 15/06/2010
18/04/2014, 15/02/2010
8/06/2014, 15/06/2010
31/12/2014, 01/01/2013
01/01/2014, 31/12/2013
];
Maybe like this:
Set DateFormat = 'DD/MM/YYYY';
LOAD *,
Age([End Date],[Start Date]) & ' Years '
&
IF(month([Start Date]) < month([End Date]),
mod((month([End Date]) - month([Start Date])) + (Day([Start Date]) > Day([End Date])),12) & ' Months ',
mod(12-(month([Start Date]) - month([End Date])) + (Day([Start Date]) > Day([End Date])),12) & ' Months ' )
&
if(Day([End Date])> Day([Start Date]), Day([End Date])-Day([Start Date]) & ' Days', day(monthend([Start Date]))-day([Start Date])+day([End Date]) & ' Days') as Diff;
LOAD * INLINE [
End Date, Start Date
18/04/2014, 15/06/2010
18/06/2014, 15/06/2010
18/04/2014, 15/02/2010
8/06/2014, 15/06/2010
31/12/2014, 01/01/2013
01/01/2014, 31/12/2013
];
thank you
There is small issue in the Days calculation, where we need to use a larger equal comparison in the if clause:
LOAD *,
Age([End Date],[Start Date]) & ' Years '
&
IF(month([Start Date]) < month([End Date]),
mod((month([End Date]) - month([Start Date])) + (Day([Start Date]) > Day([End Date])),12) & ' Months ',
mod(12-(month([Start Date]) - month([End Date])) + (Day([Start Date]) > Day([End Date])),12) & ' Months ' )
&
if(Day([End Date])>= Day([Start Date]), Day([End Date])-Day([Start Date]) & ' Days', day(monthend([Start Date]))-day([Start Date])+day([End Date]) & ' Days') as Diff;
Hello:
I need to extract the exact number of years between two date.
for Examplo,
If startdate = 30/04/2006
And
enddate = 15/04/2014
Result to obtain is 7 years and 11 months
To put a range for 1 to 7 years that person should not leave me.
Because it has more than 7 years
How do to limit that option and just go down the 7
helpme
please
Maybe using the age() function:
=age('15/04/2014','30/04/2006')
The function age does not give me the exact years,
examplo:
=age('15/04/2014','30/04/2006')
We obtain: 7 years with several months.
I need a function that I have fulfilled the seven years:
For examplo:
Of 1 to 7 years this age('15/04/2014','30/04/2006') not qualify,he have more time.
This Qualify age('15/04/2013','30/04/2006') 6 years with several months.
I need an exact function of completed years, not more
help me