Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Nov 2004
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Time Help (Excel 2003)

    Hi guys,

    I'm doing an excel sheet and I need some help in finding an Excel equation that will work with certain data. The premise is that these are call logs from a helpdesk system and I need to work out the time difference between certain events, each event having it's own column.

    So basically I have 2 columns, A and B, with a date and time in each field in the format "19/05/06 14:00". What I want to do is take one date/time away from the other to work out the difference in time between the two, in the format of "hours:minutes". So using the following example:

    "19/05/06 14:45" minus "19/05/06 14:00" equals "00:45", i.e. 45 minutes.

    However, it's not as simple as that. The date can vary between the two fields and the equation has to take into account that weekend days should not be included in the time difference and that working times are 9am to 6pm and so the hours outside these times should not be included either. For example:

    "22/05/06 10:00" minus "19/05/06 17:00" would actually equal "02:00" because only 2 working hours would have elapsed between the 2 times. Yep, it's a bit complicated.

    Thanks in advance.
    <font color=448800><font face="Comic Sans MS"><big>Lyra J </font color=448800></font face=comic></big>
    <img src=/S/flags/UK.gif border=0 alt=UK width=30 height=18> Ducking the arrows in Robin Hood country <IMG SRC=http://www.wopr.com/w3tuserpics/Lyra_J_sig.gif ALT="No, Admins, no! I'm sorry, okay!" title="No, Admins, no! I'm sorry, okay!">

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Time Help (Excel 2003)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> My little Banana Freak

    [hem] [hem] Friday Night

    Anyway try Date & Time Calculations He seems to have a rather super dooper one for you to try out...good luck
    Jerry

  3. #3
    3 Star Lounger
    Join Date
    Nov 2004
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Time Help (Excel 2003)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Hey Jerry! It's been too long, <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

    Thanks for that, I'll give it a try & see if I can work it out. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

    Missing this place. <img src=/S/sad.gif border=0 alt=sad width=15 height=15>
    <font color=448800><font face="Comic Sans MS"><big>Lyra J </font color=448800></font face=comic></big>
    <img src=/S/flags/UK.gif border=0 alt=UK width=30 height=18> Ducking the arrows in Robin Hood country <IMG SRC=http://www.wopr.com/w3tuserpics/Lyra_J_sig.gif ALT="No, Admins, no! I'm sorry, okay!" title="No, Admins, no! I'm sorry, okay!">

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Time Help (Excel 2003)

    Miss you more <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jerry

  5. #5
    Mrs Jezza
    Guest

    Re: Excel Time Help (Excel 2003)

    Now, mr Jezza Bear, cut that out immediately.

    You'll have to account for this when you get home! <img src=/S/cranky.gif border=0 alt=cranky width=18 height=25>

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Time Help (Excel 2003)

    Oh Hello Dear

    Of course I miss you more my little sweethearted chesnut coochy pumpkin
    Jerry

  7. #7
    3 Star Lounger
    Join Date
    Nov 2004
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Time Help (Excel 2003)

    <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> and <img src=/S/barf.gif border=0 alt=barf width=64 height=23>

    !

    <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Hello Mrs J. Very nice to meet you! <img src=/S/hello.gif border=0 alt=hello width=25 height=29>
    <font color=448800><font face="Comic Sans MS"><big>Lyra J </font color=448800></font face=comic></big>
    <img src=/S/flags/UK.gif border=0 alt=UK width=30 height=18> Ducking the arrows in Robin Hood country <IMG SRC=http://www.wopr.com/w3tuserpics/Lyra_J_sig.gif ALT="No, Admins, no! I'm sorry, okay!" title="No, Admins, no! I'm sorry, okay!">

  8. #8
    3 Star Lounger
    Join Date
    Nov 2004
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Time Help (Excel 2003)

    Thanks for that Jezza. It wasn't exactly what I was looking for but was good enough that I could adapt it to my needs.

    Take care <img src=/S/hugs.gif border=0 alt=hugs width=41 height=25>
    <font color=448800><font face="Comic Sans MS"><big>Lyra J </font color=448800></font face=comic></big>
    <img src=/S/flags/UK.gif border=0 alt=UK width=30 height=18> Ducking the arrows in Robin Hood country <IMG SRC=http://www.wopr.com/w3tuserpics/Lyra_J_sig.gif ALT="No, Admins, no! I'm sorry, okay!" title="No, Admins, no! I'm sorry, okay!">

  9. #9
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Time Help (Excel 2003)

    <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>
    Jerry

Posting Permissions

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