Adding time in excel

jerseyboy

New Member
Joined
Jun 20, 2014
Messages
15
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
 

Some videos you may like

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
Joined
Oct 7, 2013
Messages
777
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Attachments

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

jerseyboy

New Member
Joined
Jun 20, 2014
Messages
15
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
Joined
Oct 7, 2013
Messages
777
Office Version
  1. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Oct 7, 2013
Messages
777
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 20, 2014
Messages
15
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

negative_values.jpg
 

Marcílio_Lobão

Well-known Member
Joined
Oct 7, 2013
Messages
777
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 20, 2014
Messages
15
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

error_msg.jpg
 

Marcílio_Lobão

Well-known Member
Joined
Oct 7, 2013
Messages
777
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top