# Date Extraction

#### LeeBillington

##### Board Regular
So I'm trying to get a formula to extract a date from a second sheet labeled register but it keeps coming up with some random number "42051" which isnt even on the sheet.. anyone know where i am going wrong?

=IF(C6="","",VLOOKUP(C6,Register!\$A\$3:\$G\$400,7,FALSE))

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### JLGWhiz

##### Well-known Member
If your destination cell was formatted as date, it would probably look like a date instead of the date value, which the 42051 most likely is.

#### JackDanIce

##### Well-known Member
Try:
Code:
``=TEXT(IFERROR(VLOOKUP(C6,Register!\$A\$3:\$G\$400,7,0),""), "DD MMM YYYY HH:MM")``
Edit the formatting for the TEXT argument to suit.

##### MrExcel MVP
Just format the formula cell as date. (Dates are integers.)

#### LeeBillington

##### Board Regular
Worked thankyou :D

can you help me with another?

basically i want the date in that cell to be worked out in another cell of the exact length.
i've copied this formula from somewhere and slightly changed it but all im getting is #VALUE

=IF(B13="","",DATEDIF(B13,J1,"Y")&" Years, "&DATEDIF(B13,J1,"YM")&" Months, "&DATEDIF(B13,J1,"MD")&" Days")

#### LeeBillington

##### Board Regular
Worked thankyou :D

can you help me with another?

basically i want the date in that cell to be worked out in another cell of the exact length.
i've copied this formula from somewhere and slightly changed it but all im getting is #VALUE

=IF(B13="","",DATEDIF(B13,J1,"Y")&" Years, "&DATEDIF(B13,J1,"YM")&" Months, "&DATEDIF(B13,J1,"MD")&" Days")

##### MrExcel MVP
If B13 and J1 are true dates (numbers) and B13 <= J1, the formula will succeed as intended.

Better:

=IF(ISNUMBER(B13*J1),IF(B13 <= J1, DATEDIF(B13,J1,"Y")&" Years, "&DATEDIF(B13,J1,"YM")&" Months, "&DATEDIF(B13,J1,"MD")&" Days",""),"")

#### T. Valko

##### Well-known Member
42051 is the date serial number for Feb 16 2015.

In Excel dates are really just numbers formatted to look like dates. These numbers are commonly referred to as the date serial number. The date serial number is the count of days starting from a base date. In Windows Excel the default base date is January 1, 1900. So, January 1, 1900 is date serial number 1.

Jan 1 1900 = date serial number 1
Jan 2 1900 = date serial number 2
Jan 3 1900 = date serial number 3
Jan 4 1900 = date serial number 4
Jan 5 1900 = date serial number 5
etc
etc
Jan 1 1975 = date serial number 27395
Jan 1 2000 = date serial number 36526
Jan 1 2016 = date serial number 42370

You can see the date serial number by entering some date in a cell and then changing that cells format to General or Number.

Replies
18
Views
503
Replies
2
Views
121
Replies
3
Views
176
Replies
3
Views
204
Replies
1
Views
258

1,191,134
Messages
5,984,872
Members
439,921
Latest member
Neocold

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

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