#### jerseyboy

##### New Member
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

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### Marcílio_Lobão

##### Well-known Member
jerseyboy, Good morning.

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.

#### Attachments

• 25-02-2021_Tides.PNG
11 KB · Views: 7

#### jerseyboy

##### New Member
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

##### Well-known Member
jerseyboy,

The layout I showed you is acceptable?

#### Marcílio_Lobão

##### Well-known Member

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

##### New Member
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

##### Well-known Member

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

##### New Member
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

##### Well-known Member
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

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

Replies
1
Views
178
Replies
11
Views
131
Replies
2
Views
179
Replies
8
Views
325
Replies
2
Views
80

1,128,157
Messages
5,629,023
Members
416,359
Latest member
Juena

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back