Calculating dates in yyyymmdd format

QUINABA

Board Regular
Joined
Jul 18, 2002
Messages
127
I'm looking for a way to calculate the number of elapsed days/work days/weeks from 2 dates that are in yyyymmdd format.

Could this be done with the cell formatted as number? Do I need to format the cells in a certain way?

Thanks in advance for any help.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,910
Office Version
  1. 365
Platform
  1. Windows
As long as they are entered as dates, it doesn't matter what date format they are in, you will still be able to use all of the date functions on them.

For the number of elapsed days, simply subtract the two dates and format the result as a number. For number of workdays elapsed, see the NETWORKDAYS function in Excel help.
 

santeria

Well-known Member
Joined
Oct 7, 2003
Messages
1,844
Intriguing...

Well I formatted Cell A1 as the Given Date Format, then Cell B1 as the same. In cell C1 I put " =DATEDIF(A1,B1,"d")

The test I had was January 23 1985 in Cell A1, and January 23 1995 for Cell B1. C1 gave the result 3652.

Cell C1 was formatted as General.

(y)

(Darn... I gotta learn to type faster :)... DATEDIF was quite interesting in MR EXCEL ON EXCEL... p. 62... a few options in calculating days, weeks, months and years )

:unsure:
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
There is a NETWORKDAYS function in the Analysis ToolPak; for total days from start dt to end date you can use --

=DATEDIF(A1,B1,"D")

or

=INT(B1)-INT(A1)

where A1 is the start date, B1 the end date.

Speakin' o' time -- I'd better get faster! :)
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458

ADVERTISEMENT

QUINABA said:
I'm looking for a way to calculate the number of elapsed days/work days/weeks from 2 dates that are in yyyymmdd format.

Could this be done with the cell formatted as number? Do I need to format the cells in a certain way?

Thanks in advance for any help.

Formatting the cells doesn't change the underlying value of the cell,
Book1
HIJK
1200401022004012114
Sheet1
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
QUINABA said:
I'm looking for a way to calculate the number of elapsed days/work days/weeks from 2 dates that are in yyyymmdd format.

Could this be done with the cell formatted as number? Do I need to format the cells in a certain way?

Thanks in advance for any help.

if your looking for days elapsed as a whole number why not use the DAYS360 function. working days, how about the NETWORKDAYS function.

hope this helps.
 

QUINABA

Board Regular
Joined
Jul 18, 2002
Messages
127

ADVERTISEMENT

Thank you all for your reply. I'm still piecing together the project I'm working on and will review your solutions starting tomorrow.

Can't thank you guys enough... :pray:
 

QUINABA

Board Regular
Joined
Jul 18, 2002
Messages
127
I may have phrased my question incorrectly. The data I'm working with is hardcoded as "yyyymmdd" (ex: "20040115") which is exported from an Access database where the field is text. How do I work around this?

Could I calculate this in Access? If in Excel how.

Thanks.
 

santeria

Well-known Member
Joined
Oct 7, 2003
Messages
1,844
The formula in DATEDIF as above, will do days, weeks, months as mentioned.
All depends on if you want work days, or other items, which are ably covered by contributions of other people.


(y)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,812
Members
416,884
Latest member
leeshjay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top