Calculating dates in yyyymmdd format

[QUINABA]

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.

 

[Joe4]

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]

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.



(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 )

 

[just_jon]

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]

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
	H	I	J	K
1	20040102	20040121	14
Sheet1

 

[Zack Barresse]

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]

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... ray:

 

[QUINABA]

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]

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.

 

[BuddieB]

check out the following thread for converting text to Excel Date formats:

http://www.mrexcel.com/board2/viewtopic.php?t=72499&highlight=convert+text+date