The difference between two dates.

P

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
 
G

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.
 
G

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
 
P

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
 
G

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
 
P

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top