No of days between two dates

[BM]

Hi All,


I need to calculate the number of days between two dates.

The database is downloaded from the web.

For one reason or another, LEN=5 for some dates and LEN=10 for the rest.

My formula is

=IF(LEN(M15)=5,M15,DATEVALUE(MID(M15,4,2)&"-"&LEFT(M15,2)&"-"&RIGHT(M15,4)))-IF(LEN(L15)=5,L15,DATEVALUE(MID(L15,4,2)&"-"&LEFT(L15,2)&"-"&RIGHT(L15,4)))


Is there a shorter formula?...


Thank you.

 

[Jesse]

Hi BM,


Excel date values are coded in a consecutive number system, so you can subtract one date from the other with the result being the number of days in between. Your values need to be formatted as dates.


Hopefully this helps, otherwise post a link to your spreadsheet so we can have a look.

 

[Hui]

BM


In addition to what Jesse said you can also use

=NETWORKDAYS.INTL(Start Date,End Date)

use =NETWORKDAYS(Start Date,End Date) in older Excel versions


The advantage of using networkdays is that you can use optional Weekends and Holidays

eg: =NETWORKDAYS(Start Date,End Date, Weekend, Holidays)

Where the Weekend can be specified as any days in the week Refer Excel help

Holidays can be a Range of date defining the holidays


Networkdays returns the days excluding weekends and holidays

 

[BM]

*embarassed* how do i share excel file in the forum? I cannot see any option for me to click...

 

[Debraj]

HI BM,


No Need to embarrassed Just read below topic for detail..


http://chandoo.org/forums/topic/posting-a-sample-workbook


Regards,

Deb

 

[BM]

Thanks Deb.

http://www.2shared.com/file/Y4aFwkaL/Book2.html


Hi Jesse and Hui, I am unable to do a simple subtraction...

 

[Hui]

BM


In D2: =B2-A2

Copy down


If you don't want to include Weekends

=NETWORKDAYS.INTL(A2,B2,1)

 

[NARAYANK991]

Hi BM ,


I don't think there can be a shorter formula , if I have understood your problem correctly.


The information you have given viz. that in some cases , the length of your data is 5 , and in some cases it is 10 , leads me to believe that your date format is mm-dd-yyyy. Dates in Excel are just numbers ; a valid date such as November 22 , 2012 ( today's date ) is just a number 41235 , formatted to display in any valid date format. However , in my system , since my date format is mm/dd/yyyy , this will be recognized by Excel as a valid date only if I enter it as 11/22/2012 or in any of the other acceptable variants , such as Nov-22 , 22-Nov ,... However , if I enter 22/11/2012 , Excel cannot interpret it as a valid date ; instead it takes this as a piece of text.


A valid date , which is just a number , will have 5 digits ; at least , any date after May 17 , 1927 !


Any date which has been interpreted by Excel as text , can have 9 or 10 digits , depending on whether you use the leading 0 for single-digit values.


So we have four possible conditions :


1. Data in both the columns ( e.g. A2 and B2 ) are valid dates


2. Data in the first cell is a valid date , while the data in the other cell is text


3. Data in the first cell is text , while data in the other cell is a valid date


4. Data in both the cells is text


Any correct formula has to take care of all 4 conditions ; with this constraint , whether you can have a shorter formula is a difficult question to answer ; in my opinion NO. You may get a formula which is a few characters less in length if you use the DATE function instead of the DATEVALUE function , but I don't think that is a great improvement. Of course , the easiest way to reduce the lengths of formulae is to use helper cells / columns.


Narayan

 

[BM]

Thanks Hui. The formula works on some rows but not on others.


Hi Narayan, yes you describe the situation very well! Thanks and well noted.

 

[Hui]

BM

If you have a mixture of dates and text that looks like dates but is infact text

Select the Data in Column A

Goto Data, Text to Column and go through the dialogue and select the correct date format

Repeat for the other column

That should fix your data

 

[BM]

Hui, that works too! wow thanks.

 

[BM]

oh... i just realized i need to repeat the text-to-col step twice for each column before i can get the correct answer?..

 

[NARAYANK991]

Hi ,


You need to be careful !


On my system , the default date format is mm/dd/yyyy ; suppose I have a date , which has been entered as 11/6/2012 , which is to be interpreted correctly as November 6 , 2012. Along with this valid date , I also have a date in text format as 23/07/2012 , which is to be interpreted as July 23 , 2012.


Assuming that I now use the Text to Columns feature , and specify DMY as the date format , I will certainly get 23/07/2012 converted to 07/23/2012 , which is the right date ; however , 11/6/2012 will get converted to 06/11/2012 , which is June 11 , 2012 ! Certainly not what was intended originally.


Narayan

 

[BM]

Correct narayan! the first time i did text-to-col, i got the wrong answer! And had to do text-to-col one more time before i get the correct answer...