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

 

[pgc01]

Hi

Similar to Marcelos's

=DATE(1900+LEFT(A1,3),1,RIGHT(A1,3))

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

B1 is a date, not a string. If what you want is the day use:

=DAY(B1)

 

[Salamullah]

Thanks all for your help

For learning purpose I tried all given ideas, Good to see all are working.

 

[Marcelo Branco]

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

Hi,

You cannot use LEFT to get the Day of a date, because 04/01/2011 is only a friendly viewing of the number 40547, the Serial Number for this date.

Excel, in fact, works with 40547, so when you use LEFT(B1,2) it returns 40.

To get the day of B1 try this in C1
=DAY(B1)

HTH

M.

 

[Salamullah]

Yes done the same

Thanks