VBA has a function called DateDiff:
DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])
The DateDiff function syntax has these named arguments:
Part
Description
interval Required. String expression that is the interval of time you use to
calculate the difference between date1 and date2.
date1, date2 Required; Variant (Date). Two dates you want to use in the
calculation.
firstdayofweek Optional. A constant that specifies the first day of the week.
If not specified, Sunday is assumed.
firstweekofyear Optional. A constant that specifies the first week of the year.
If not specified, the first week is assumed to be the week in which January 1
occurs.
Settings
The interval argument has these settings:
Setting
Description
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second
If you're stuck with the month/day/year in separate cells,
You can use:
D1 = DateValue(Cells(32, 2) & "/" & Cells(33, 2) & "/" & Cells(34, 2))
D2 = DateValue(Cells(32, 3) & "/" & Cells(33, 3) & "/" & Cells(34, 3))
MsgBox DateDiff("d", D1, D2)
If you REALLY, REALLY, REALLY want to use your M1,D1,Y1, you can use:
M1 = Cells(32, 2).Value
D1 = Cells(33, 2).Value
Y1 = Cells(34, 2).Value
M2 = Cells(32, 4).Value
D2 = Cells(33, 4).Value
Y2 = Cells(34, 4).Value
D1 = DateValue(M1 & "/" & D1 & "/" & Y1)
D2 = DateValue(M2 & "/" & D2 & "/" & Y2)
MsgBox DateDiff("d", D1, D2)
HTH
Paul
----- Original Message ----
> From: larry <[EMAIL PROTECTED]>
> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> Sent: Monday, December 8, 2008 8:18:36 AM
> Subject: $$Excel-Macros$$ Re: run time type mismatch/other improvements
>
>
> Thanks for the tips. When I checked the code, I found I had reversed
> the row and column in the Cell(). There were also a few other issues.
> I like the Date - Date but that would not teach me anything about
> programming. I still need to cehck the if then sequence but it does
> run with some of the test problems.
>
> On Dec 5, 2:13 am, lohith wrote:
> > Hi Larry,
> >
> > Try Using this formula instead.
> >
> > =DATE(B4,B3,B2)-DATE(D4,D3,D2)
> >
> > Cheers,
> > Lohith
> >
> > On Dec 5, 12:51 am, larry wrote:
> >
> >
> >
> > > I am trying to creat a code for caculating the number of days between
> > > two dates. N=A-N causes a type mismacth run time error. Also looking
> > > for any other improvements. Nested if may need to be cleared up.
> >
> > > Thanks for any help.
> >
> > > Option Explicit
> > > Public M1 As Integer, M2 As Integer, D1 As Integer, D2 As Integer, Y1
> > > As Variant, Y2 As Variant
> > > Public N As Integer, A As Integer
> > > Sub DayDelta()
> > > M1 = Cells(2, 2).Value
> > > D1 = Cells(3, 2).Value
> > > Y1 = Cells(4, 2).Value
> > > M2 = Cells(2, 4).Value
> > > D2 = Cells(3, 4).Value
> > > Y2 = Cells(4, 4).Value
> > > Call Daycalc(M1, D1, Y1, A)
> > > N = A
> > > Call Daycalc(M2, D2, Y2, A)
> > > N = A - N
> > > N = Cells(3, 5).Value
> > > End Sub
> > > Sub Daycalc(M, D, Y, A)
> > > Public M As Integer, D As Integer, Y As Variant, A As Integer
> >
> > > Select Case M
> > > Case 1
> > > A = 0
> > > Case 2
> > > A = 31
> > > Case 3
> > > A = 59
> > > Case 4
> > > A = 90
> > > Case 5
> > > A = 120
> > > Case 6
> > > A = 151
> > > Case 7
> > > A = 181
> > > Case 8
> > > A = 212
> > > Case 9
> > > A = 243
> > > Case 10
> > > A = 273
> > > Case 11
> > > A = 304
> > > Case 12
> > > A = 334
> > > End Select
> > > A = A + Y * 365 + Int(Y / 4) + D + 1 - Int(Y / 100) + Int(Y / 400)
> > > If Int(Y / 4) = Y / 4 Then
> > > If Y / 400 = Int(Y / 400) Then
> > > If M < 3 Then
> > > A = A - 1
> > > End If
> > > Else
> > > If Y / 100 <> Int(Y / 100) Then
> > > If M < 3 Then
> > > A = A - 1
> > > End If
> > > End If
> > > End Sub- Hide quoted text -
> >
> > - Show quoted text -
>
>
--~--~---------~--~----~------------~-------~--~----~
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/excel-macros?hl=en
Visit & Join Our Orkut Community at
http://www.orkut.com/Community.aspx?cmm=22913620
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com
To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
-~----------~----~----~----~------~----~------~--~---
