CALCULATION OF HOURS IN EXCEL

alisdair4

alisdair4

Well-Known Member
Joined
18 Jan 2004
Messages
690
Location
Isle of Bute
midnightdrifter.net
Has anyone worked out a way of subtracting hours in Excel? I want to create a spreadsheet into which you enter the time of HW (Dover, in this case), and have Excel provide the 6 hours before and after. This would then be printed and slotted into my tidal stream atlas to save me working it out. (Yes, I know it's sad...!)
However, while the programme will let you subtract one time from another, I cannot see a way of subtracting an hour from a fixed time. I am sure this has been done before, but cannot find anything on the search function.
 
If you put =[cell ref]-"01:00"
note: the speach marks seem to be the key in the forumla

Then it will subtract 1 hour. It will put the cell in date and time format, but you can then reformat the cell by selecting 'Format', 'Cells', 'Number' tab, and select 'Time' on the left

Hope this helps

Jonny
 
First of all you need to format all the cells to be used in a suitable format, in "custom" in the format cells menu you will find hh:mm which should do. The all you need to do is set up the formula in the box you want the answer in which would be for example A1+06:00 and so on. this should work
 
I do this all the time - it's so useful especially on long trips and have a template that makes it easy.

I have a dedicated cell at the top set to 01:00 and the format set to hh:mm

I then use an absolute reference to this e.g.

=B15-$G$1 where G1 is my cell and B15 is the time I want to subtract an hour from

Once you have the formula in absolute format like this (F4 when in the formula bar to save typing the $) the formula can be copied around the spreadsheet

I just plug in the time on the HWs and the sheet works out (apart from when times go back over midnight or foerward over midnight - I haven't figured that one out yet, and manually adjust!)

So my sheet looks like this:

HW+6
HW-6
HW-5
HW-4
HW-3
HW-2
HW-1
HW - I just fill in here and all the other cells fill
HW+1
HW+2
HW+3
HW+4
HW+5
HW+5
HW+6
 
The Excel format for elapsed time is [hh:mm]

Use format of hh:mm for the cells containing the times and [hh:mm] for cells where you do any calcs. Note that the elapsed times rolls over when it gets to 24 hours
 
One way to have it roll over backwards

e.g. for 2am minus 6hrs

Both cells formatted hh:mm
Enter hours in first cell (say A1) as hours plus 24 e.g. for 2:00 enter 28:00. This will be 1 day plus 2hrs but will display as 2:00 hrs 'cos the cell is set to display only hrs.
In second cell (say A2) enter formula =A1-"6:00"

This works because it stops Excel from trying to go back in time before its day 1 and reporting an error.

When you've got the hang of this you can make your spreadsheet estimate the depths as well using the rule of 12ths.
 
Excel struggles with the concept of negative time. Basically, it just gives up and puts a load of #s in.

[hh]:mm format will happily add hours together and keep going with 24, 25, 26, etc when it gets to 24, while hh:mm will go 22, 23, 00, 01...

If you have a cell, say A1 with HW Dover in it then put the formula =a1+1 on another sell, say B9. In B8 put in =b9-1/24 and copy the formula up into B7-B4. In B10, put =b9+1/24 and copy down.

I haven't got time to sort out a way of coming up with a list of ports and differences at the moment - positive differences are easy, negative ones are more difficult, but PM me if you'd like me to sort something outwhen I get home. It'll probably involve some hidden cells and general trickery!

This should give you what you want.
 
Excel can subtract times.
If you select Tools Options
Choose the calc tab and select the 1904 date system
Now try keying times in HH:MM format.

Format result to [hh]:mm
 
You must log in or register to reply here.

Share this page

Top