How to substract dates with excel - current date with date in format yyyyddmmhhmmss

Question

Is there any option to compare dates with Date format yyyymmddhhmmss with the current date? Basically one of my external source have this type of date and I have to compare this date with the current date and check difference in between. I have tried to split those date with LEFT,MID,RIGHT functions, so basically, I have two columns - first with date, second with time, but I cannot find any option to subtract current date with date in column, because results are not coming correct.

Sample of date: 20161112203545

after splitting: 2016-11-12 20:05:45.

Any ideas?

Does timezone or daylight savings matter? I ask because depending on where these spreadsheets come from, where you are, and how far apart the dates are it may matter. — n8wrl

skkakkar skkakkar · Accepted Answer · 2016-05-16T19:40:38

Image produced below with formulas is self explanatory.

Your date :20161112203545 in D4

Formula to convert date in E4 :

=DATE(LEFT(D4,4),MID(D4,5,2),MID(D4,7,2))+TIME(MID(D4,9,2),MID(D4,11,2),RIGHT(D4,2))

Today's Date in F4 : =TODAY()

Formula to get date difference in days in G4 : =DATEDIF(F4,E4,"d")

EDIT

The alternative to Excel DATEDIF would be a User defined function (UDF) that internally uses the VBA DATEDIFF function:

This UDF accepts three parameters:

Start_Date: The days from which the period begins. End_Date: It is the last date of the period that you wish to calculate. Unit: It specifies the interval by which you want the difference. Here the unit accepts following values. Value Description YYYY Year Q Quarter M Month Y Day of year D Day

Public Function xlDATEDIF(Start_Date As Date, End_Date As Date, Unit As String) As String  
    xlDATEDIF = DateDiff(Unit, Start_Date, End_Date)  
End Function

In this case usage will be, put formula in H4 =xlDATEDIF(F4,E4,"D") HTH

How to substract dates with excel - current date with date in format yyyyddmmhhmmss

2 Answers