I am trying get the macro to tell me the difference between two dates.
ie, the difference between 20/01/2007 and 22/01/2007 is two days.

Any help would be much appreciated.

Thanks,

Peter

Just subtract them and format the cell as General.

Peter wrote:

I am trying get the macro to tell me the difference between two dates.
ie, the difference between 20/01/2007 and 22/01/2007 is two days.

Any help would be much appreciated.

Thanks,

Peter

--

Dave Peterson

MsgBox #1/22/2007# - #1/20/2007#
MsgBox DateDiff("d", #1/20/2007#, #1/22/2007#)

Either would do - half the fun is getting the date into a format readable by
VBA and/or Excel. DateDiff has other periods you can enter. Other useful
functions include DateValue and DateSerial. Help's pretty good on these.

"Peter" wrote:

I am trying get the macro to tell me the difference between two dates.
ie, the difference between 20/01/2007 and 22/01/2007 is two days.

Any help would be much appreciated.

Thanks,

Peter

A1: 20/01/2007
A2: 22/01/2007

msgbox Range("A2").Value2 - Range("A1").Value2

this assumes your regional settings are in dd/mm/yyyy

--
Regards,
Tom Ogilvy


"Peter" wrote:

I am trying get the macro to tell me the difference between two dates.
ie, the difference between 20/01/2007 and 22/01/2007 is two days.

Any help would be much appreciated.

Thanks,

Peter

On 1 Feb, 16:06, Tom Ogilvy
wrote:
A1: 20/01/2007
A2: 22/01/2007

msgbox Range("A2").Value2 - Range("A1").Value2

this assumes your regional settings are in dd/mm/yyyy

--
Regards,
Tom Ogilvy



"Peter" wrote:
I am trying get the macro to tell me the difference between two dates.
ie, the difference between 20/01/2007 and 22/01/2007 is two days.

Any help would be much appreciated.

Thanks,

Peter- Hide quoted text -

- Show quoted text -

All,

Thanks for your help.

This is based on Smallweed's code:

Sub DateDiff()

a = "02/1/2007"
b = "20/1/2007"

MsgBox DateDiff("d", a, b)

End Sub

I assume you saying that doesn't work as I wouldn't expect it to.

first, you named your procedure datediff which causes an error. Name it
something else.

This adjustment should work:

Sub DateCheck()

a = "2/1/2007"
b = "20/1/2007"

MsgBox DateDiff("d", CDate(a), CDate(b))

End Sub

--
Regards,
Tom Ogilvy


"Peter" wrote:

On 1 Feb, 16:06, Tom Ogilvy
wrote:
A1: 20/01/2007
A2: 22/01/2007

msgbox Range("A2").Value2 - Range("A1").Value2

this assumes your regional settings are in dd/mm/yyyy

--
Regards,
Tom Ogilvy



"Peter" wrote:
I am trying get the macro to tell me the difference between two dates.
ie, the difference between 20/01/2007 and 22/01/2007 is two days.

Any help would be much appreciated.

Thanks,

Peter- Hide quoted text -

- Show quoted text -

All,

Thanks for your help.

This is based on Smallweed's code:

Sub DateDiff()

a = "02/1/2007"
b = "20/1/2007"

MsgBox DateDiff("d", a, b)

End Sub

On 1 Feb, 17:31, Tom Ogilvy
wrote:
I assume you saying that doesn't work as I wouldn't expect it to.

first, you named your procedure datediff which causes an error. Name it
something else.

This adjustment should work:

Sub DateCheck()

a = "2/1/2007"
b = "20/1/2007"

MsgBox DateDiff("d", CDate(a), CDate(b))

End Sub

--
Regards,
Tom Ogilvy

"Peter" wrote:
On 1 Feb, 16:06, Tom Ogilvy
wrote:
A1: 20/01/2007
A2: 22/01/2007

msgbox Range("A2").Value2 - Range("A1").Value2

this assumes your regional settings are in dd/mm/yyyy

--
Regards,
Tom Ogilvy

"Peter" wrote:
I am trying get the macro to tell me the difference between two dates.
ie, the difference between 20/01/2007 and 22/01/2007 is two days.

Any help would be much appreciated.

Thanks,

Peter- Hide quoted text -

- Show quoted text -

All,

Thanks for your help.

This is based on Smallweed's code:

Sub DateDiff()

a = "02/1/2007"
b = "20/1/2007"

MsgBox DateDiff("d", a, b)

End Sub

Tom, thank you for pointing out the error with the title. The code
works with this exception.