DateDiff to produce answer in 'yrs, mths, dys'

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rigby - 28 Feb 2006 14:26 GMT

Hi

This expression below works perfectly, but what I need to add to it, which I
seem to have difficulty with, is to show how many days as well.

=DateDiff("m",[Date of Joining],Now())\12 & " yrs " & DateDiff("m",[Date of
Joining],Now()) Mod 12 & " mths"

Please can someone show me how to produce an answer like:
'19 yrs 6 mths 10 dys' (for example)

Kind regards

Rigby

Reply to this Message

Ofer - 28 Feb 2006 14:41 GMT

Try this link

http://www.accessmvp.com/djsteele/Diff2Dates.html

Signature

\\// Live Long and Prosper \\//
BS"D

> Hi
>
[quoted text clipped - 10 lines]
>
> Rigby

Reply to this Message

rigby - 28 Feb 2006 14:56 GMT

Thank you, I had actually just found it and used it. It does what I want BUT,
it is returning the wrong number of days. it says that from date1 (eg. 9 Dec
1996) to Now() = 9 yrs 2 mths 8 dys
which is wrong. it should be '19 dys'

Any ideas as to why it does this?

Thank you for your quick and helpful response.

Rigby

> Try this link
>
[quoted text clipped - 14 lines]
> >
> > Rigby

Reply to this Message

Ofer - 28 Feb 2006 15:12 GMT

I tried it and it gave me the right resault, mybe check the date in your
computer.

Signature

\\// Live Long and Prosper \\//
BS"D

> Thank you, I had actually just found it and used it. It does what I want BUT,
> it is returning the wrong number of days. it says that from date1 (eg. 9 Dec
[quoted text clipped - 25 lines]
> > >
> > > Rigby

Reply to this Message

rigby - 28 Feb 2006 15:39 GMT

I checked my date and time settings on my computer and they are all as
normal. I am set to UK date format, will this make a difference. I ask as I
just copied and pasted and adjusted control names to fit the code, I am not
well educated with VBA.

please advise mighty Ofer :]

Kind Regards

Rigby

> I tried it and it gave me the right resault, mybe check the date in your
> computer.
[quoted text clipped - 28 lines]
> > > >
> > > > Rigby

Reply to this Message

Ofer - 28 Feb 2006 17:01 GMT

I also copy and paste the code, and try the Immidiate window with

?Diff2Dates("ymd", #09/12/1996#, Now())

Try this and tell me what do you get

Signature

\\// Live Long and Prosper \\//
BS"D

> I checked my date and time settings on my computer and they are all as
> normal. I am set to UK date format, will this make a difference. I ask as I
[quoted text clipped - 39 lines]
> > > > >
> > > > > Rigby

Reply to this Message