Extrapolating the number of days

[Fred Djinn Holstings]

I was wondering how I would extrapolate how many days and what days of the
week are within a range span. A scenario like...

Cell E13 Cell F13
01/26/2007 3:41 PM 01/29/2007 12:41 PM

Between the two dates are Friday, Saturday, Sunday, and Monday or
numerically 6, 7, 1, 2 for a total of four days but really 2 days and 21
hours.

The VBA code I'm thinking of is this...

dStart = Range("E13")
dEnd = Range("F13")
Set dCount = 0
Set hCount = 0
Set mCount = 0

For Each Weekday(Date) in dStart to dEnd
Set dCount = Weekdays(Days) ***what do i do here to get the result?
Set hCount = Weekdays(Hours) ***what do i do here to get the result?
Set mCount = Weekdays(Minutes) ***what do i do here to get the result?
MsgBox "There are " & dCount & " Days and " & hCount & " Hours and " &
mCount & " Minutes in this selection boss"


As you can see I'm a real novice and I am quite certain I'm all sorts of
wrong but I have the idea and I'm wondering how I can complete it.


Thanks In Advance,
Fred

 

[George Nicholson]

Lots of possible approaches, here is one:

Sub CalcElapsed()
Dim dtmStart As Date
Dim dtmEnd As Date
Dim iDays As Integer
Dim iHours As Integer
Dim iMinutes As Integer
Dim lngTotalMinutes As Long

dtmStart = ActiveSheet.Range("B40")
dtmEnd = ActiveSheet.Range("C40")

lngTotalMinutes = DateDiff("n", dtmStart, dtmEnd)
iMinutes = lngTotalMinutes Mod 60
iHours = Fix(lngTotalMinutes / 60) '60 min/hour
iDays = Fix(lngTotalMinutes / 3600) '3600 min/day

MsgBox iDays & " days, " & iHours & " hours, " & iMinutes & " minutes
elapsed."
End Sub

HTH,