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

Discuss the spreadsheet application
Post Reply
Leiif
Posts: 2
Joined: Mon Aug 16, 2010 3:57 pm

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

Post by Leiif »

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
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

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

Post by keme »

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 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
Leiif
Posts: 2
Joined: Mon Aug 16, 2010 3:57 pm

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

Post by Leiif »

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
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

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

Post by keme »

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
Right, you asked for "subtract" and i explained how to add. Glad that you're better at math than I am at reading :)

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 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
Richarda44
Posts: 351
Joined: Sat May 24, 2008 6:59 pm

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

Post by Richarda44 »

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
Post Reply