Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extrapolating the number of days
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extrapolating the number of days
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, "Fred "Djinn" Holstings" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pulling Data off Web - Need Function Help | Excel Worksheet Functions | |||
Calculating Number of days from one date to another | Excel Discussion (Misc queries) | |||
Number of days left in the Month? | Excel Discussion (Misc queries) | |||
Number of Days in the Month | Excel Discussion (Misc queries) | |||
to find number of days between 2 dates using vba code in excel | Excel Discussion (Misc queries) |