Adding time in excel

[jerseyboy]

I have a column of times [these are high and low tides for each day in the calendar year, eg High 6:35 AM, Low 12:15 PM]

I also have a list of adjustment factors, measured in minutes and seconds [these are to display the reference times for another location, eg High -3:35, Low -4:19]

I can do this for dozens of measurement stations, so I really hope excel can do the heavy lifting for me

Example:

Reference site: H: 6:35 AM. L: 12:15 PM
Site 1 factors: H: -3.35, L: -4:19 --> Site 1 tides: H:6:31:25 AM, L: 12:10:41 AM
Site 2 factors: H: +2:15; L: +2:09 -->Site 2 tides: H:6:37:15 AM, L: 12:17:09 AM

 

[Marcílio_Lobão]

jerseyboy, Good morning.

Please, I have a question:

Are the factors fixed for each site or they change every day?

Some example attached. (Sorry, I can't install Xl2BB at corporate machine)

Remember "Use 1904 data system at Excel" to obtain a negative time in a cell.

Is this a kind of help for you?

I hope hear from you soon.

 

[jerseyboy]

Hello, the factors are fixed . . . high tide site 1 is always 3:35 earlier than the reference site high tide; Low tide site 2 is always 2:09 later than the reference site

... so your example is correct; looking for the formula to make this work.

 

[Marcílio_Lobão]

jerseyboy,

Well. What's your necessity about your Excell file ?

The layout I showed you is acceptable?

Please, tell us more about you excel file.

 

[Marcílio_Lobão]

Jerseyboy,

It is only necessary to add the time of the reference site with the differences in each site.

Based on my example:

Site 1
E4 --> =B4 + $E$3
F4 --> =C4 + $F$3

Site 2
H4 --> =B4 + $H$3
I4 --> =C4 + $I$3

Formulas are prepared to push it down as necessary.

I hope it help.

 

[jerseyboy]

I get all of this. My issue is excel wants a leading apostrophe in front of negative time values. I have found a workaround using iserror() and find(“-“,...)

=IF(ISERROR(FIND("-",C$1)),C$1,-1*ABS(RIGHT(C$1,8)))+$A2

yes this workaround works but it's ugly

 

[Marcílio_Lobão]

jerseyboy, Good morning.

It was not necessary to have all this work.
I told you in the previous post how to arrange this.

Remember "Use 1904 data system at Excel" to obtain a negative time in a cell.

Fix this problem doing this:
--------------------------------------------------------------------------------------------------------------------------
1) Click File tab, and select Options from the menu list. And the Excel Options dialog will open.

2) Click Advanced from the left pane, and check Use 1904 data system under When calculating this workbook
section.

3) Click Ok button.
--------------------------------------------------------------------------------------------------------------------------
Then all the negative times will be shown normally.

Please, tell us if it worked for you.

I hope it helps.

 

[jerseyboy]

Your excel works differently from mine.

Entering -00:03:35 into a cell generates an error message. I can only get the value to display via math:

A1 00:00:00
A2 00:03:35
A3 =A1-A2

This does yield the desired result:

0:00:00​

0:03:35​

-0:03:35​

But just entering the value in the cell yields this msg

 

[Marcílio_Lobão]

jerseyboy, Good afternoon.

Your excel works differently from mine.

It sounds very strange.

Did you try to activate the function that I mentioned in the previous post in your windows default?

Change this function in the advanced settings and then you can enter any negative time that will work perfectly.

I'm sorry that for some very strange reason your windows don't want to work as I always managed to make it work.

I'm here if you have another question.

Have a nice day.

 

[jerseyboy]

Yes—I used 1904; otherwise my workaround wouldn’t have worked