Date , time formula

[subrosumon]

Hi Experts,
Sorry for wastage you valuable time for my critical issues! as a HR staff, i need to calculate actual staying time if he intermittent input in my organization
for example

Name of Organization	Period

<tbody>
[TD="width: 348, bgcolor: transparent"] ABC Company
[/TD]
[TD="width: 348, bgcolor: transparent"] from 3 October 2010 to 25 January 2014

[/TD]

[TD="width: 348, bgcolor: transparent"] XYZ Company
[/TD]
[TD="width: 348, bgcolor: transparent"] from 28 January 2014 to 12 December 2015

[/TD]

[TD="width: 348, bgcolor: transparent"] ABC Company
[/TD]
[TD="width: 348, bgcolor: transparent"] from 13 December 2015 to 18 September December 2019

[/TD]

</tbody>

Needto calculation

1. How many years, months and days he is working all company (total Experience)?
2. How many years, months and days he is working of ABC company?

if it is formula it s better or me

Tanks in advance

 

[mole999]

are your dates in different fields. Then for the first one I would be looking at 5/7ths and if greater then 365 remove the approximate leave allocation. It would provide something rough with quite a lot of effort

 

[subrosumon]

yes, it is different fields

 

[mole999]

so highest minus lowest give number of days, as a first starting point, into another column, then with a sumif or similar to add each group together

 

[subrosumon]

so highest minus lowest give number of days, as a first starting point, into another column, then with a sumif or similar to add each group together

Sorry sir, i can't doing this, can you please help to send this?

 

[mole999]

where do you intend to store the result, are your dates actually dates or just text, what do you have as examples you can show

 

[subrosumon]

Dear Sir,
Here is the template f u please create to motion column and rows, I can use the formula
[TABLE="width: 433"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]1
[/TD]
[TD="width: 64, bgcolor: transparent"]B
[/TD]
[TD="width: 65, bgcolor: transparent"]C
[/TD]
[TD="width: 64, bgcolor: transparent"]D
[/TD]
[TD="width: 64, bgcolor: transparent"]E
[/TD]
[TD="width: 64, bgcolor: transparent"]F
[/TD]
[TD="width: 64, bgcolor: transparent"]G
[/TD]
[TD="width: 64, bgcolor: transparent"]H
[/TD]
[TD="width: 64, bgcolor: transparent"]I
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 3"]Joining Date
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 3"]End Date
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Day
[/TD]
[TD="bgcolor: transparent"]Month
[/TD]
[TD="bgcolor: transparent"]Year
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Day
[/TD]
[TD="bgcolor: transparent"]Month
[/TD]
[TD="bgcolor: transparent"]Year
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[TD="bgcolor: transparent"]ABC
[/TD]
[TD="bgcolor: transparent, align: right"]15
[/TD]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[TD="bgcolor: transparent, align: right"]2011
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]25
[/TD]
[TD="bgcolor: transparent, align: right"]9
[/TD]
[TD="bgcolor: transparent, align: right"]2015
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[TD="bgcolor: transparent"]XYZ
[/TD]
[TD="bgcolor: transparent, align: right"]28
[/TD]
[TD="bgcolor: transparent, align: right"]11
[/TD]
[TD="bgcolor: transparent, align: right"]2015
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]21
[/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[TD="bgcolor: transparent, align: right"]2018
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6
[/TD]
[TD="bgcolor: transparent"]ABC
[/TD]
[TD="bgcolor: transparent, align: right"]22
[/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[TD="bgcolor: transparent, align: right"]2018
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]31
[/TD]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent, align: right"]2019
[/TD]
[/TR]
</tbody>[/TABLE]

 

[mole999]

in i =DATEVALUE(G4&"/"&H4&"/"&I4)-DATEVALUE(C4&"/"&D4&"/"&E4)

 

[subrosumon]

Thank you Sir,
My result is showing #VALUE!, can you please help me.

 

[steve the fish]

Try this:

=SUMPRODUCT((DATE(I4:I6,H4:H6,G4:G6)-DATE(E4:E6,D4:D6,C4:C6)+1)*(B4:B6="ABC"))