Date Extraction

[LeeBillington]

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

 

[JLGWhiz]

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]

Try:

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

 

[Aladin Akyurek]

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

 

[LeeBillington]

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]

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

 

[Aladin Akyurek]

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]

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.