Date , time formula

subrosumon

Board Regular
Joined
Mar 20, 2014
Messages
122
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

ABC Company
from 3 October 2010 to 25 January 2014

XYZ Company
from 28 January 2014 to 12 December 2015

ABC Company
from 13 December 2015 to 18 September December 2019


<tbody>
</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

 
Last edited:

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,334
Office Version
2019, 2016, 2013
Platform
Windows
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
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,334
Office Version
2019, 2016, 2013
Platform
Windows
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

Board Regular
Joined
Mar 20, 2014
Messages
122
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

Moderator
Joined
Oct 23, 2004
Messages
10,334
Office Version
2019, 2016, 2013
Platform
Windows
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

Board Regular
Joined
Mar 20, 2014
Messages
122
Dear Sir,
Here is the template f u please create to motion column and rows, I can use the formula
1
B
C
D
E
F
G
H
I
2
Joining Date
End Date
3
Day
Month
Year
Day
Month
Year
4
ABC
15
5
2011
25
9
2015
5
XYZ
28
11
2015
21
4
2018
6
ABC
22
4
2018
31
7
2019

<tbody>
</tbody>
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,334
Office Version
2019, 2016, 2013
Platform
Windows
in i =DATEVALUE(G4&"/"&H4&"/"&I4)-DATEVALUE(C4&"/"&D4&"/"&E4)
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,072
Office Version
365
Platform
Windows
Try this:

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

Watch MrExcel Video

Forum statistics

Threads
1,098,859
Messages
5,465,111
Members
406,412
Latest member
superjoejoe

This Week's Hot Topics

Top