# Calculating dates in yyyymmdd format

#### QUINABA

##### Board Regular
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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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.

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 )

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!

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

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.

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:

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.

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.

Replies
3
Views
192
Replies
1
Views
97
Replies
4
Views
273
Replies
3
Views
832
Replies
1
Views
454

1,217,677
Messages
6,137,941
Members
450,100
Latest member
mscetr

### 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.

### Which adblocker are you using?

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

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