converting dates to decimal years

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

davidbaker - 04 Jul 2006 14:42 GMT

i need to convert dates dd/mm/yyyy to decimal years yy.yyyy but cannot
find an example of how this can be done. i would be grateful for
help?
thanks

Signature

davidbaker

Reply to this Message

Bob Phillips - 04 Jul 2006 15:02 GMT

Perhaps this

=TEXT(A8,"yyyy")+(A8-DATE(YEAR(A8),1,0))/(DATE(YEAR(A8)+1,1,0)-DATE(YEAR(A8)
,1,0))

and format as required

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> i need to convert dates dd/mm/yyyy to decimal years yy.yyyy but cannot
> find an example of how this can be done. i would be grateful for
> help?
> thanks

Reply to this Message

davidbaker - 05 Jul 2006 14:58 GMT

thanks for that, but i am afraid this does not work as expected. if i
operate on 01/10/1998 the script returns 20/06/1905

further help needed !
thanks

Bob Phillips Wrote:

> Perhaps this
>
[quoted text clipped - 28 lines]
> > View this thread:
> http://www.excelforum.com/showthread.php?threadid=558168

Signature

davidbaker

Reply to this Message

Bob Phillips - 05 Jul 2006 15:05 GMT

As I said you need to format as required. You have it formatted as date.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> thanks for that, but i am afraid this does not work as expected. if i
> operate on 01/10/1998 the script returns 20/06/1905
[quoted text clipped - 4 lines]
> Bob Phillips Wrote:
> > Perhaps this

=TEXT(A8,"yyyy")+(A8-DATE(YEAR(A8),1,0))/(DATE(YEAR(A8)+1,1,0)-DATE(YEAR(A8)

> > ,1,0))
> >
[quoted text clipped - 25 lines]
> > > View this thread:
> > http://www.excelforum.com/showthread.php?threadid=558168

http://www.excelforum.com/member.php?action=getinfo&userid=36024

> View this thread: http://www.excelforum.com/showthread.php?threadid=558168

Reply to this Message

daddylonglegs - 05 Jul 2006 15:20 GMT

What result do you expect for 01/10/1998?

Perhaps

=A1/365.2425+YEAR(0)

format as number

Signature

daddylonglegs

Reply to this Message

davidbaker - 05 Jul 2006 15:35 GMT

perfect thanks Bob

Signature

davidbaker

Reply to this Message