Results 1 to 4 of 4

Thread: working with times and dates
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2013
    Location
    South Wales
    Posts
    615
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default working with times and dates

    Hi All

    I've learned quite a lot about working with dates but never worked with times.

    I have to do some tidal calculations and need to calculate a time 3 hours and 5 mins before high water (I literally have to deduct 3 hours and 5 mins from the high water time which I have in column B) the problem is that if the high water time is say 02.30 hrs, deducting 3 hours and 5 mins the date would be the day before, the dates are in column A.

    What I would like to do is in column E have the correct date, the date will always be the same as that in column A unless deducting 3 hours and 5 mins moves it back to day before and enter this in column F have the revised time.

    Sounds simple, I've been at this all afternoon going around in ever decreasing circles and not getting anywhere.

    Is there a simple solution to this?? I am on the verge of throwing my laptop out of the window.

    Any help would be greatly appreciated

    Cheers

    Paul
    Last edited by paulsolar; Sep 10th, 2019 at 12:19 PM.

  2. #2
    Board Regular
    Join Date
    Oct 2011
    Posts
    4,258
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: working with times and dates

    Something like this?

    ABCDEF
    1DateTime HT DateTime
    29/2/20195:00 AM 9/2/20191:55 AM
    39/2/20192:30 AM 9/1/201911:25 PM
    49/6/20198:00 PM 9/6/20194:55 PM
    59/8/20191:25 AM 9/7/201910:20 PM

    Spreadsheet Formulas
    CellFormula
    E2=IF(B2>$H$1,A2,A2-1)
    F2=IF(B2>=$H$1,B2-$H$1,24-($H$1-B3))


    Excel tables to the web >> Excel Jeanie HTML 4

  3. #3
    Board Regular
    Join Date
    Jul 2014
    Location
    Memphis, TN
    Posts
    2,677
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: working with times and dates

    Well Date and Time is really a single value. Its just like any other number where Date is to the left of the decimal and Time is to the Right.

    Using Ahoy's mockup,
    Code:
    =A+B-TIMEVALUE("3:05")
    Format to include the display of Time such as "m/d/yyyy h:mm AM/PM"
    Excel 2013, 2016 with PowerBI
    Knowing that it can be done is half the battle!

  4. #4
    Board Regular
    Join Date
    Aug 2013
    Location
    South Wales
    Posts
    615
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: working with times and dates

    Hi Guys

    thanks very much for you replies. I have managed to get it done now with your help. I was having a real problem with this.

    Thanks Again

    Paul

Some videos you may like

User Tag List

Tags for this Thread

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
  •