I know excel can calculate the number of days between two dates when the dates are properly displayed.
However, I now have an excel with dates listed as follows
pair1 20160127
pair1 20180731
pair2 20160224
pair2 20160601
pair3 20170208
pair3 20170501
How do I calculate the number of days between these pairs ?
Any suggestion or formula ?
Maybe like this...
C1 and down =(MID(B1,5,2)&"/"&RIGHT(B1,2)&"/"&LEFT(B1,4))+0
D1 and down =IF(A1=A2,C2-C1,"")
HTH
Regards, Jeff
@Jeffrey, thanks a lot !
Q1 Is it correct that Excel recognizes (#/#/#) as (month/day/year) [*ignore the ()] ?
Q2 What is "+0" for
Q1: Yes, but Excel also has regional settings specific to your area.
Q2: +0 converts the text string into a number
Edit: Excel recognizes a date as a number. Today is 43437. Tomorrow will be 43438.
The format you see is exactly that, a format, but Excel still just sees a number as the date.
http://www.cpearson.com/Excel/datetime.htm
Last edited by jeffreybrown; 12-03-2018 at 12:41 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks