Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Excel date/time computing

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I need help converting a date and time to total hours on a job but if it spans past the normal work hours to only add actual work hours (not evening/weekend hours)
    EX:
    04/02/2002 09:41 AM
    04/04/2002 10:42 AM
    If normal work hours are 6:00 AM - 2:00 PM the total time should be 17 hr 1 min

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    Try:

    =NETWORKDAYS($A$1,$A$2)*(8/24) and Custom format as "[h]:mm:ss"

    This assumes the normal working hours are 8, eg 6am-2pm

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I tried this but it only gives me the total days (in hours ie 16:00) instead of the time elapsed including the minutes

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try:

    ="14:00"-TEXT(A1,"h:mm")+((NETWORKDAYS(A1,A2)-2)*"8:00")+(TEXT(A2,"h:mm")-"6:00")

    format as [h]:mm

    I haven't tested it over a weekend or holiday, just with what you gave, let me know if you need any more.

    "Have a good time......all the time"
    Ian Mac

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    I decided to write my first user defined function. I think it actually works.
    Does not do holidays yet. Let me know if it works and I'll figure out how to add holidays as well.

    Copy this code into a standard module or in your personal macro book.

    Call it like a normal function.

    =nhw(BeginningDateTime,EndingDateTime)

    if the beginning date and time is in cell A1
    ending date and time is in B1

    then place:
    = nhw(A1,B1)
    in another cell
    Will output "17 hrs. - 1 min."
    Tested over a weekend as well.

    Will not allow an invalid beginning or ending time to be entered. After 2 PM, before 6 am, or on a weekend day.

    Let me know if it works.

    Function nhw(StartingTime As Date, EndingTime As Date)
    Dim TotalNetHoursWorked
    Dim ThisDayEnd As Date
    Dim ThisDayBegin As Date
    Dim Cntr As Integer

    If StartingTime = 0 Then Exit Function
    If EndingTime = 0 Then Exit Function
    If Format(StartingTime, "DDDD") = "Saturday" Or _
    Format(StartingTime, "DDDD") = "Sunday" Then
    MsgBox "Starting Date on " & Format(StartingTime, "DDDD") & " is Invalid"
    nhw = "Invalid Starting Date"
    Exit Function
    End If
    If Format(EndingTime, "DDDD") = "Saturday" Or _
    Format(EndingTime, "DDDD") = "Sunday" Then
    MsgBox "Ending Date on " & Format(EndingTime, "DDDD") & " is Invalid"
    nhw = "Invalid Ending Date"
    Exit Function
    End If
    If Hour(StartingTime) < 6 Or Hour(StartingTime) > 14 Then
    MsgBox "Starting Time is invalid. Must be between 06:00 AM to 02:00 PM"
    nhw = "Invalid Starting Time"
    Exit Function
    End If
    If Hour(EndingTime) < 6 Or Hour(EndingTime) > 14 Then
    MsgBox "Ending Time is invalid. Must be between 06:00 AM to 02:00 PM"
    nhw = "Invalid Ending Time"
    Exit Function
    End If
    If DateDiff("d", StartingTime, EndingTime) > 365 Then
    MsgBox "Out of range - Greater than one year"
    nhw = "Invalid Ending Time"
    Exit Function
    End If
    ThisDayEnd = Month(StartingTime) & "/" & Day(StartingTime) & "/" & _
    Year(StartingTime) & " " & "14:00:00 PM"
    If ThisDayEnd > EndingTime Then
    TotalNetHoursWorked = DateDiff("n", StartingTime, EndingTime)
    GoTo AllDone
    End If

    TotalNetHoursWorked = DateDiff("n", StartingTime, ThisDayEnd)
    For Cntr = 1 To 365
    ThisDayBegin = Month(StartingTime + Cntr) & "/" & Day(StartingTime + Cntr) & "/" & _
    Year(StartingTime + Cntr) & " " & "06:00:00 AM"
    ThisDayEnd = ThisDayBegin + #8:00:00 AM#
    If ThisDayBegin > EndingTime Then Exit For
    If ThisDayEnd > EndingTime Then
    If Format(ThisDayEnd, "MMMM") = "Saturday" Then Exit For
    TotalNetHoursWorked = TotalNetHoursWorked + DateDiff("n", ThisDayBegin, EndingTime)
    Exit For
    End If
    If Format(ThisDayEnd, "DDDD") <> "Saturday" And _
    Format(ThisDayEnd, "DDDD") <> "Sunday" Then _
    TotalNetHoursWorked = TotalNetHoursWorked + DateDiff("n", ThisDayBegin, ThisDayEnd)
    Next
    AllDone:
    nhw = Int(TotalNetHoursWorked / 60) & " hrs. - " & TotalNetHoursWorked Mod 60 & " min."
    End Function

    Tom


    [ This Message was edited by: TsTom on 2002-04-09 09:27 ]

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the answers. They both worked great. I do, however, love the one with the weekends. If it would not be too much trouble I would love to have the holidays also figured. You mentioned that you could probably do so. If not, then thanks for all your help. It was greatly appreciated.

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    Several of us offered to include holidays.
    Which function did you use and what are your companies holidays?
    Tom

  8. #8
    New Member
    Join Date
    Apr 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    TsTom,
    I used yours. Holidays are Jan 1, Memorial day (this year May 27), July 4, Labor Day (this year Sep 2), Thanksgiving & day after (Nov 28, 29), Christmas (Dec 24-31).

    Again, Thanks so much.

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi sweethrtcc.
    I have 'till memorial day to get his figured out, right?
    Any one want to risk a migraine and make my function work with holidays???
    Dave Hawley! Do not make fun of Tom's first function! Remember This!
    I can do in 100 lines, what you can do in two! Am tired. Later...

    Will Post it here on May 6
    Search for your username sweethrtcc
    Or you can E-Mail me
    TsTom@hotmail.com
    Tom

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •