## [Solved] Formula to subtract a set number of hours from time

### [Solved] Formula to subtract a set number of hours from time

Hello,

Every week I need to produce a spreadsheet which contains various columns. The information is gathered from the internet and I can usually download most of the info easily. However, lately the site I get the info from has been doing the TIME column in GMT. I need a way(formula) to subtract 4 hours from that column and have it fill another column with the adjusted values not with the formula that created them. The final sheet needs to NOT have the original GMT times showing. That can be deleted but only if it won't mess my values up that were calculated.

I did do a search of the board and tried several things but I am getting weird results like -83:00:00

Thanks,
Leiif
Last edited by Hagar Delest on Sun Aug 29, 2010 4:11 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 3.2 on Windows XP sp3
Leiif

Posts: 2
Joined: Mon Aug 16, 2010 3:57 pm

### Re: I need a formula to subtract a set number of hours from

One pitfall that easily messes up time calculations:
The "time unit" in spreadsheets is one day (24 hours). If you subtract 4 from a cell containing the time 13:00:00, you are really subtracting 96 hours (4 times 24), yielding your -83:00:00.
When dealing with timespans across midnight, you need to account for that in your formula, by either including dates in the calculation, or adding/subtracting (possibly a multiple of) 24 hours.

You can add 4 hours as "+1/6" (because 4 hours is one sixth of a day), or as "+TIME(4;0;0)" (the TIME() function is specifically made to convert "human time units" to the spreadsheet time data format).

I hope this helps. If you need more specific help, post again, specifying your data in more detail.
Apache OO 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14

keme
Volunteer

Posts: 2801
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

### Re: I need a formula to subtract a set number of hours from

I used the simple formula of =B2-1/6 . Worked great. Then I copied the cells and special pasted with only date & time checked. Problem solved and thanks a lot.

Best,
Leiif
OpenOffice 3.2 on Windows XP sp3
Leiif

Posts: 2
Joined: Mon Aug 16, 2010 3:57 pm

### Re: I need a formula to subtract a set number of hours from

Leiif wrote:I used the simple formula of =B2-1/6 . Worked great. Then I copied the cells and special pasted with only date & time checked. Problem solved and thanks a lot.

Best,
Leiif

Now if you will, edit your first post and add the word [solved] in the subject line. It improves the quality of the site.
Apache OO 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14

keme
Volunteer

Posts: 2801
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

### Re: I need a formula to subtract a set number of hours from

To get round potential 24 hour math problems,
When adding or subtracting times, I use a logical formula "-(b10>1)" result to either subtract 1 (if true) or 0 (if false)
OOo 4.1.1. bld 9593 on MS Windows 10 64 SP1 Chillblast fusion i5 and Acer Aspire E1-572 Laptop

There's got to be a better way
And for all accountants - The change is coming
Richarda44

Posts: 351
Joined: Sat May 24, 2008 6:59 pm