CODEWITHSUNDEEP
excelexcel-formulaexcel-2010vba
user4572896
user4572896

Reputation: 141

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

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?

Upvotes: 0

Views: 472

Answers (2)

skkakkar
skkakkar

Reputation: 2828

Image produced below with formulas is self explanatory.

finding difference in dates

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

Upvotes: 1

cdipaola
cdipaola

Reputation: 23

Taking the date as

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

Is going to cause you some issues as Excel assigns date values with a serial number, and it's going to throw that number off. You could use the =today() function and set it up where you have the date entered, say it is in cell A1, then =A1-today() (formatted as a number) should give you the difference in the amount of days. Microsoft explanation of using Dates in Excel

Upvotes: 0

Related Questions