Issue in date format

[prakash]

Hi there,

One of the system generated excel has given me an date column as dd.mm.yyyy(i.e 01.01.2001). My objective is to find the total experience of employees out of 2 columns with similar date format. Could someone help me on this. Thanks in advance.

Regards
SP

 

[Deepak]

Hi,

It would nice to share a sample.

 

[prakash]

Hi,

It would nice to share a sample.

Hi Deepak,

Below is the sample of data workings. I need to arrive total experience from the career start & end date.

Pls refer the img attached for better understanding.

Employee Career Start Date Career End Date Total experience (in months)
1 02.08.1988 08.06.1997
2 01.05.1995 28.06.1998
3 10.04.1995 13.04.2000


Regards
SP

 

[bosco_yip]

Maybe,

D2, copy down :

=DATEDIF(SUBSTITUTE(B2,".","/"),SUBSTITUTE(C2,".","/"),"m")

Regards

 

[prakash]

Hi,

Thanks for your resolution.

This formula doesn't work on columns A3 & A4.

Regards
SP

 

[Chihiro]

@prakash

Bosco's formula works if your system's regional setting uses date format of d/M/yyyy. If not... replace SUBSTITUTE() with DATE(TEXTfunctions).

Something like below.

=DATEDIF(DATE(RIGHT(B2,4),MID(B2,4,2),LEFT(B2,2)),DATE(RIGHT(C2,4),MID(C2,4,2),LEFT(C2,2)),"m")

 

[prakash]

Hi Chihiro,

This is awesome

Regards
SP