The difference between two dates.

[Peter]

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]

Just subtract them and format the cell as General.

 

[Guest]

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.

 

[Guest]

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

 

[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






- 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

 

[Guest]

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

 

[Peter]

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

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