Results 1 to 10 of 10

Thread: Excel - Subtract time from date & time but display result as date & time - help!

  1. #1
    ɯʎɔɐɹsɐʌʍ mycarsavw's Avatar
    Join Date
    Feb 2007
    Posts
    4,945
    Thanks
    1,097
    Thanked
    653 times in 482 posts
    • mycarsavw's system
      • Motherboard:
      • P8H77-M Pro
      • CPU:
      • i5 3350P
      • Memory:
      • 16Gb
      • Storage:
      • Lots
      • Graphics card(s):
      • R9 285
      • PSU:
      • HX 620w
      • Case:
      • FD Define Mini
      • Operating System:
      • W10
      • Monitor(s):
      • BenQ G2420HDBL + GL2450HT
      • Internet:
      • Sky

    Excel - Subtract time from date & time but display result as date & time - help!

    Greatest thread title ever?

    Not really sure how to word this one so bear with me.

    I want an object to arrive at its destination at a given time and date hh:mm:ss - dd:mm:yy - call this ETA

    The time it takes to arrive varies depending on where it's sent from but will always be shown in hours, minutes and seconds - [hh]:mm:ss - call this travel time

    Using the above information I want to find out the time and day I'd have to send the object to arrive at the given ETA

    What formula would I use to subtract travel time from ETA and display the result as a date and time (hh:mm:ss dd:mm:yy).

    I think that makes sense but I'm sure someone will tell me if it doesn't

    Thanks in advance.
    |Kata: "Read title as 'fisting'. Not sure why I clicked. Relieved, really."|
    |TAKTAK: "It was so small that mine wouldn't fit into it"|

  2. #2
    Funking Prink! Raz316's Avatar
    Join Date
    Jul 2003
    Location
    Deal, Kent, UK
    Posts
    2,978
    Thanks
    130
    Thanked
    62 times in 52 posts

    Re: Excel - Subtract time from date & time but display result as date & time - help!

    just tried it in excel with these custom field settings

    (A1) ETA column = dd/mm/yyyy hh:mm:ss
    (B1) travel time = hh:mm:ss

    if I set a column to the equation of =sum(A1-B1) I get the result I am after. Unless I am missing something here? : o

    edit: (I know your field settings are different, but ive just tried it and it still works)

  3. Received thanks from:

    mycarsavw (26-06-2009)

  4. #3
    ɯʎɔɐɹsɐʌʍ mycarsavw's Avatar
    Join Date
    Feb 2007
    Posts
    4,945
    Thanks
    1,097
    Thanked
    653 times in 482 posts
    • mycarsavw's system
      • Motherboard:
      • P8H77-M Pro
      • CPU:
      • i5 3350P
      • Memory:
      • 16Gb
      • Storage:
      • Lots
      • Graphics card(s):
      • R9 285
      • PSU:
      • HX 620w
      • Case:
      • FD Define Mini
      • Operating System:
      • W10
      • Monitor(s):
      • BenQ G2420HDBL + GL2450HT
      • Internet:
      • Sky

    Re: Excel - Subtract time from date & time but display result as date & time - help!

    You're missing nothing and thanks

    I was initially trying to do this using Google Documents and it just wouldn't give me the answer - it kept telling me the ETA field wasn't a number.

    Doing it in Excel it works fine as you confirmed but I was hoping some smart person would show me a long winded formula that'd work in Google Documents too.
    |Kata: "Read title as 'fisting'. Not sure why I clicked. Relieved, really."|
    |TAKTAK: "It was so small that mine wouldn't fit into it"|

  5. #4
    Mostly Me Lucio's Avatar
    Join Date
    Mar 2007
    Location
    Tring
    Posts
    5,163
    Thanks
    443
    Thanked
    448 times in 351 posts
    • Lucio's system
      • Motherboard:
      • Gigabyte GA-970A-UD3P
      • CPU:
      • AMD FX-6350 with Cooler Master Seldon 240
      • Memory:
      • 2x4GB Corsair DDR3 Vengeance
      • Storage:
      • 128GB Toshiba, 2.5" SSD, 1TB WD Blue WD10EZEX, 500GB Seagate Baracuda 7200.11
      • Graphics card(s):
      • Sapphire R9 270X 4GB
      • PSU:
      • 600W Silverstone Strider SST-ST60F
      • Case:
      • Cooler Master HAF XB
      • Operating System:
      • Windows 8.1 64Bit
      • Monitor(s):
      • Samsung 2032BW, 1680 x 1050
      • Internet:
      • 16Mb Plusnet

    Re: Excel - Subtract time from date & time but display result as date & time - help!

    Why not use the TIME() function with a bit of string slicing via MID() to grab the relevant sections if GoogleDocs isn't co-operating

    something like A1-TIME(MID(B1,1,2),MID(B1,4,2),MID(B1,7,2))??

    You'll need to ensure consistant formating on the columns for the MID to work, otherwise you get into the fun of writing a formula to find the appropriate parts.

    (\___/) (\___/) (\___/) (\___/) (\___/) (\___/) (\___/)
    (='.'=) (='.'=) (='.'=) (='.'=) (='.'=) (='.'=) (='.'=)
    (")_(") (")_(") (")_(") (")_(") (")_(") (")_(") (")_(")


    This is bunny and friends. He is fed up waiting for everyone to help him out, and decided to help himself instead!

  6. Received thanks from:

    mycarsavw (26-06-2009)

  7. #5
    ɯʎɔɐɹsɐʌʍ mycarsavw's Avatar
    Join Date
    Feb 2007
    Posts
    4,945
    Thanks
    1,097
    Thanked
    653 times in 482 posts
    • mycarsavw's system
      • Motherboard:
      • P8H77-M Pro
      • CPU:
      • i5 3350P
      • Memory:
      • 16Gb
      • Storage:
      • Lots
      • Graphics card(s):
      • R9 285
      • PSU:
      • HX 620w
      • Case:
      • FD Define Mini
      • Operating System:
      • W10
      • Monitor(s):
      • BenQ G2420HDBL + GL2450HT
      • Internet:
      • Sky

    Re: Excel - Subtract time from date & time but display result as date & time - help!

    Ooh, now we're getting interesting.

    It works perfectly in Excel and EditGrid (online version of Excel with the option to share worksheets in real time) but again GoogleDocs doesn't want to play - it just returns #VALUE! and tells me that the value in the ETA column is not a number.

    I'll keep on trying though, there has to be a way round it.
    |Kata: "Read title as 'fisting'. Not sure why I clicked. Relieved, really."|
    |TAKTAK: "It was so small that mine wouldn't fit into it"|

  8. #6
    Senior[ish] Member Singh400's Avatar
    Join Date
    Jun 2008
    Posts
    2,933
    Thanks
    136
    Thanked
    310 times in 247 posts

    Re: Excel - Subtract time from date & time but display result as date & time - help!

    This may be of some help too.

    If it is working on EditGrid and Excel. Why are you bothering with G-Docs?

  9. Received thanks from:

    mycarsavw (26-06-2009)

  10. #7
    ɯʎɔɐɹsɐʌʍ mycarsavw's Avatar
    Join Date
    Feb 2007
    Posts
    4,945
    Thanks
    1,097
    Thanked
    653 times in 482 posts
    • mycarsavw's system
      • Motherboard:
      • P8H77-M Pro
      • CPU:
      • i5 3350P
      • Memory:
      • 16Gb
      • Storage:
      • Lots
      • Graphics card(s):
      • R9 285
      • PSU:
      • HX 620w
      • Case:
      • FD Define Mini
      • Operating System:
      • W10
      • Monitor(s):
      • BenQ G2420HDBL + GL2450HT
      • Internet:
      • Sky

    Re: Excel - Subtract time from date & time but display result as date & time - help!

    To prove that GoogleDocs isn't as useless as "people" think - but that's not happening, yet
    |Kata: "Read title as 'fisting'. Not sure why I clicked. Relieved, really."|
    |TAKTAK: "It was so small that mine wouldn't fit into it"|

  11. #8
    Senior[ish] Member Singh400's Avatar
    Join Date
    Jun 2008
    Posts
    2,933
    Thanks
    136
    Thanked
    310 times in 247 posts

    Re: Excel - Subtract time from date & time but display result as date & time - help!

    I think you have just proved G-Docs is useless. It's fruitless mission mate.

    Does G-Docs support the =TEXT cmd? Then you could just format the output so that it displays the raw output to however you want (shown on that link).

  12. #9
    Senior[ish] Member Singh400's Avatar
    Join Date
    Jun 2008
    Posts
    2,933
    Thanks
    136
    Thanked
    310 times in 247 posts

    Re: Excel - Subtract time from date & time but display result as date & time - help!

    Ah mate, you must be doing something wrong in G-Docs. I've just tried it and it works fine!



    Edit* Added an screenshot to show my awesomeness
    Last edited by Singh400; 26-06-2009 at 03:05 PM.

  13. Received thanks from:

    mycarsavw (26-06-2009)

  14. #10
    ɯʎɔɐɹsɐʌʍ mycarsavw's Avatar
    Join Date
    Feb 2007
    Posts
    4,945
    Thanks
    1,097
    Thanked
    653 times in 482 posts
    • mycarsavw's system
      • Motherboard:
      • P8H77-M Pro
      • CPU:
      • i5 3350P
      • Memory:
      • 16Gb
      • Storage:
      • Lots
      • Graphics card(s):
      • R9 285
      • PSU:
      • HX 620w
      • Case:
      • FD Define Mini
      • Operating System:
      • W10
      • Monitor(s):
      • BenQ G2420HDBL + GL2450HT
      • Internet:
      • Sky

    Re: Excel - Subtract time from date & time but display result as date & time - help!

    Doh - so simple - thanks and yes, you be awesome

    EDIT:

    So I've finally worked out what was stopping it from calculating. My methodology was correct but my data entry wasn't quite as accurate as Google wanted. It's as simple as using A1-B1 but you have to enter the whole time (hh:mm:ss) regardless of whether there are minutes/seconds.

    I was just entering 28/06/09 21:00 as you would in Excel.

    Once you enter the 28/06/09 21:00:00 it all clicks into place.

    Cheers folks - a productive Friday has been saved
    Last edited by mycarsavw; 26-06-2009 at 03:26 PM.
    |Kata: "Read title as 'fisting'. Not sure why I clicked. Relieved, really."|
    |TAKTAK: "It was so small that mine wouldn't fit into it"|

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. The Well Dodgy Joke Thread
    By 0iD in forum General Discussion
    Replies: 1246
    Last Post: 08-04-2009, 01:42 PM
  2. ATI Catalyst 5.8 released
    By =TcQi= in forum Graphics Cards
    Replies: 17
    Last Post: 18-08-2005, 12:35 AM
  3. CAT 4.6's
    By Steve in forum Graphics Cards
    Replies: 18
    Last Post: 13-06-2004, 11:36 AM

Posting Permissions

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