Date Difference

[#1]

I have a Two Column Date of Joining and Date of Resignation ...

Example :Date of Joining - 01/01/2012  And  Date of Resignation - 02/05/2015

so i want the Out Put how many Year Month and Day Employee works.. like .. 3 year 4 Month and 1 Day ...

Plz Tell me

[#2]

Hello Brijesh,

in you script(Where you load your data) add a column and based on Anbu's add the below.

Floor(Interval([Date of Joining]-[Date of Resignation],'DD')/365) & ' Years ' & Floor(Mod(Interval([Date of Joining]-[Date of Resignation],'DD'),365)/30) & ' Months ' & Mod(Mod(Interval([Date of Joining]-[Date of Resignation],'DD'),365),30) & ' Days ' as [Total Number of Period Person worked]

hop this helps

[#3]

Excel ... I have a Two column .. one of Date of Joining and Second of Date of Resignation.. so i need to find out total

how many period employee worked..

Kindly tell me... Thanks

[anbu1984]

Load *,Floor(Days/365) & ' Years ' & Floor(Mod(Days,365)/30) & ' Months ' &Mod(Mod(Days,365),30) & ' Days ';
Load *,Interval(DOR-DOJ,'DD') As Days;
lOAD Date#(DOJ,'DD/MM/YYYY') As DOJ,Date#(DOR,'DD/MM/YYYY') As DOR;

Load DOJ,DOR From Excel.xlsx; //Edit Script --> Table Files --> File Wizard -- This will automatically create load statement

[#5]

so first i need to load my date data

Load DOJ,DOR,

Date#(DOJ,'DD/MM/YYYY') As DOJ1,

Date#(DOR,'DD/MM/YYYY') As DOR1,

Interval(DOR1-DOJ1,'DD') As Days

From Excel.xlsx;

BUT while Reloading its giving me a Error Field not found - <DOR1>...

kinldy help me how to write this code....

[anbu1984]

Load DOJ,DOR,

Date#(DOJ,'DD/MM/YYYY') As DOJ1,

Date#(DOR,'DD/MM/YYYY') As DOR1,

Interval(DOR1-DOJ1,'DD') As Days //You are getting error since DOR1 is not present in your input excel

From Excel.xlsx;

Calculate difference in preceding load

Load *, Interval(DOR1-DOJ1,'DD') As Days; //Preceding Load

Load DOJ,DOR,

Date#(DOJ,'DD/MM/YYYY') As DOJ1,

Date#(DOR,'DD/MM/YYYY') As DOR1

From Excel.xlsx;

[#7]

if i m doing this ways its not showing any data of Days:

DOJ	DOR	DOJ1	DOR1	Days
2/4/2012	3/2/2015	41001	42038	-
3/4/2012	4/2/2015	41002	42039	-
6/5/2012	5/6/2015	41035	42160	-
9/6/2012	6/9/2015	41069	42253	-
12/9/2012	9/12/2015	41164	42347	-
4/10/2012	10/4/2015	41186	42104	-
5/1/2015	8/5/2015	42009	42132	-
3/7/2015	7/3/2015	42188	42070	-
3/7/2015	8/5/2015	42188	42132	-
5/8/2015	8/5/2015	42221	42132	-

[#8]

its giving me a Data.. But I have date format DD/MM/YYYY.. and its giving a Data in MM/DD/YYYY Format...kinldy help Me

[anbu1984]

Can you post sample excel file

[#10]

DOJ	DOR
3/12/2011	29/05/2012
1/12/2013	3/5/2015
4/4/2011	6/7/2014
29/01/2013	31/12/2014
1/5/2011	5/5/2014

[anbu1984]

When I pasted above data into excel, some of the rows have date and others are general. Its better you keep data in one format.

Can you attach sample excel file.