working with times and dates

[paulsolar]

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

 

[AhoyNC]

Something like this?

Excel Workbook
	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
Sheet

 

[SpillerBD]

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,

=A+B-TIMEVALUE("3:05")

Format to include the display of Time such as "m/d/yyyy h:mm AM/PM"

 

[paulsolar]

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