How do I get Excel to to work with + & - time for tide information past midnight and after Midnight
Excel uses an integer number to keep track of dates, basically adding 1 each day that passes. "Integer number" — that suggests a thought, eh? Date and time, integer for dates... Excel uses the decimal place side of a value to store the time.
So 44127.5263 is day 44127 since 1-1-1900 plus 0.5263 of a day since that. If you multiply the decimal by 24 you see how many hours: 12.6312. It is this decimal that you are using when you enter time. As a "by the way" that means that when you add or subtract hours from it, you need to use 1/24, not 1. 2/24, not 2, and so on.
When you enter a date and time together in a single cell, you get the full integer and decimal portion. Most people find that awkward to do and for that reason, along with at least one more small reason, usually enter a date in one cell and any times in other cells.
If you enter them separately, Excel only puts in what you entered. So if you enter 10/14/2021 at about 4:58 pm, it puts an integer in the cell. Date only, no decimal portion, so you don't carry along the 4:58 pm part which would, in any case, make using the date rather harder to do and so would cut down on functionality. So that's a good thing. It does the same for time: enter 4:58 on 10/14/2021 and it just enters the time, the decimal part, and does not include the date you entered it on for all the same reasons as for why it did the date only when you just entered a date.
So enter 4:00 am and you get a value of 0.166666666666666 in the cell. Subtract 6 hours (6/24 = 0.25) and you get a negative number. Excel has two date systems it is happy to use, but one of them is really only for people stuck with Mac/Apple products. The one commonly used by folks starts in 1900 and has the unhappy feature that it will NOT use a negative date or time in ANY way (unless you convert the entries to text). The other system starts in 1904 (so all dates already entered would suddenly be wrong which means if you are going to use it, you usually need to begin when you create the spreadsheet, not three years later!), but WILL use negative dates and times in calculations.
So, you can stop right here and do that. Then your times will be fine when negative. However... you will certainly end up wishing you did not do so. I'll leave it at that: you will wish you hadn't for all the trouble the unusual choice makes for you.
So, you might have to do it anyway though, if a simple workaround is not available. Happily, one IS available.
Two scenarios, one simply a teensy bit more complicated. Which applies depends upon your desires.
- You do NOT need or want dates involved AT ALL. You perhaps use the spreadsheet as a scratch pad to enter the time and generate with formulas the plus and minus hour times. That info is then used elsewhere and not in any way that requires a history of it be kept in this spreadsheet. The spreadsheet is just a calculator for you, nothing more.
In that case, note that your times will either be yesterday and today, today only, or today and tomorrow. If today only, no problem since there are no negatives involved. If today and tomorrow, no problem either as the value goes above 1, not below 0. For the yesterday and today problem, just add 1 to the value before calculating. So, say you have 1:00 am entered. Your formula for -3 hours might look like this:
= 1 + time - 3 hours
If the time entry were in cell A1, that might look like so:
= 1 + A1 - 3/24
Instead of -0.0833333333333333 you'd get +0.916666666666667. So instead of the error (that no one calls an error...) of "############" filling the cell, no matter how wide you make it, you'd see 22:00 in the cell.
(The same formula works for all three situations, so no need for concern about when to use two different formulas.)
So you might see a list like: 19:12, 20:12, 21:12, 22:12, 23:12, 0:12, 1:12, 2:12, 3:12, 4:12, 5:12, 6:12, 7:12. Which I believe is what you want.
-
Or you might want the date after all. If so, you can have a cell for the date, perhaps right before the time cell or right after it. Remembering that it has a (positive) number value, it can take the place of the "1" you would use in the previous example. So, if the date is in cell A1 and the time in cell B1, the actual formula (for subtracting three hours) would be:
= A1 + B1 - 3/24
See how the "A1" fits in instead of a "1"?
In both cases, you then format the output cells for just what you want shown. The plus/minus hour cells might be formatted for 24 hour time, am/pm time, might include the date, or the day of the week... whatever fits your need. Notice that all that variation comes just from simply formatting the cells. Nothing extra is needed in the formulas in them!
I have the feeling you're fairly new to Excel so I will mention one other thing. You can make the formula a tiny bit "slicker": Excel uses numbers to refer to the rows and columns it shows in its grid. The columns have letters at the top of them, but that's just for us. To Excel, column A is column 1 and so on.
And it has a function to return the column number. So if you are in column F, the COLUMN() function would return a 6. Why mention that? What's it do for you?
Well, say you enter the date in A1, and time in A2. You then have 13 more cells in the row that show the -6hr, -5hr, -4hr, -3hr, -2hr, -1hr, the entered time, +1hr, and so on times that are calculated. So, what if you could get Excel to calculate all 13 of those using the same formula in each cell, not 13 different ones to get the right number of hours added and subtracted?
If you lay that out, the entered time would fall in column I and if using a formula for it, you'd want to add 0/24 to it. COLUMN() returns a 9 in column I so adding that and subtracting 9 would be a net of 0. In the -1hr column (column H) COLUMN() would add 8 and subtracting the same 9 would be a net of -1 so you'd be subtracting the 1 hour you want to subtract. Moving the other way, the +2hr column would be column K and returns an 11 but subtracting the same 9 leaves a net of... +2. So this would be your formula:
=$A1 + $B1 + ( COLUMN() -9 )/24
Just that slight difference and you can paste that in one cell, then copy it and paste into the rest instead of editing 13 cells. (Those $'s before the "A" in "$A1" and "$B1" are to keep those the same as you copy into cells C1 through O1.)
In any case, there are other variations you might need, and the devil is always, as they say, in the details, but this sounds like what you are looking for. It is also the "Why?" so might be helpful to you for other things involving dates and times.