Numbers to date format

[Salamullah]

Hi all,

I have a list of dates in number formate, I need a formula to view these dates in a normal date formate, can anybody help me to do that.

Date Number Actual Date Explaination
dd/mm/yyyy

109152 01/06/2009 109 = year 2009, 152 number of days
110031 31/01/2010
111365 31/12/2011

Thanks in advance

 

[Marcelo Branco]

Hi,

Maybe this

A B
<TABLE style="WIDTH: 104pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=139><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 width=64 align=right>109152</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=75 align=right>01/06/2009</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 align=right>110031</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>31/01/2010</TD></TR>

<TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2802983 class=xl66 height=20 align=right>111365</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>31/12/2011</TD></TR></TBODY></TABLE>

Formula in B1
=DATE(1900+LEFT(A1,3),1,1)+RIGHT(A1,3)-1
copy down

HTH

M.

 

[Salamullah]

Excellent !!

Its workable as per my need.

Thanks a lot again.

 

[Marcelo Branco]

Excellent !!

Its workable as per my need.

Thanks a lot again.

You are welcome and tks for the feedback

M.

 

[villy]

You can try this...

=IF(RIGHT(B23,3)>"090",DAY(RIGHT(B23,3)+1)&"/"&MONTH(RIGHT(B23,3)+1)&"/"&SUM(YEAR(LEFT(B23,3)),LEFT(B23,3)),DAY(RIGHT(B23,3))&"/"&MONTH(RIGHT(B23,3))&"/"&SUM(YEAR(LEFT(B23,3)),LEFT(B23,3)))

 

[Salamullah]

Villy

Thank you very much for this, your given formula is also working perfectly.

Regards

 

[villy]

Villy

Thank you very much for this, your given formula is also working perfectly.

Regards

Thanks for appreciation...

 

[d_bht]

You keep/place your cursor on the cell where you have entered the date and just press ctrl+ to show the dates again or you go to Home - Number format dropdown, then you select either short date or long date as you desired. Hope it may help you.

Hi all,

I have a list of dates in number formate, I need a formula to view these dates in a normal date formate, can anybody help me to do that.

Date Number Actual Date Explaination
dd/mm/yyyy

109152 01/06/2009 109 = year 2009, 152 number of days
110031 31/01/2010
111365 31/12/2011

Thanks in advance

 

[Salamullah]

When I am apply left digit formula, the result is coming with date formate, but I need only numaric 2 digits.

In cell A1
111004

Formula in B1
=DATE(1900+LEFT(A1,3),1,1)+RIGHT(A1,3)-1

Result in B1
04/01/2011

Formula in C1
=left(B1,2)

Result comming in C1
40

Required result
04

 

[villy]

When I am apply left digit formula, the result is coming with date formate, but I need only numaric 2 digits.

In cell A1
111004

Formula in B1
=DATE(1900+LEFT(A1,3),1,1)+RIGHT(A1,3)-1

Result in B1
04/01/2011

Formula in C1
=left(B1,2)

Result comming in C1
40

Required result
04

You can use the formula I did:
[B1]=IF(RIGHT(A1,3)>"090",DAY(RIGHT(A1,3)+1)&"/"&MONTH(RIGHT(A1,3)+1)&"/"&SUM(YEAR(LEFT(A1,3)),LEFT(A1,3)),DAY(RIGHT(A1,3))&"/"&MONTH(RIGHT(A1,3))&"/"&SUM(YEAR(LEFT(A1,3)),LEFT(A1,3)))

Then...
[C1]=VALUE(LEFT(B1,SEARCH("/",B1)-1))

Then...

Custom Format= 0#

I know a little quite long but it works...