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

1. ## 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

2. ## Re: working with times and dates

Something like this?

 A B C D E F 1 Date Time HT Date Time 2 9/2/2019 5:00 AM 9/2/2019 1:55 AM 3 9/2/2019 2:30 AM 9/1/2019 11:25 PM 4 9/6/2019 8:00 PM 9/6/2019 4:55 PM 5 9/8/2019 1:25 AM 9/7/2019 10:20 PM

 Cell Formula 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. ## 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"

4. ## 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