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

Discuss the spreadsheet application

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

Postby Leiif » Mon Aug 16, 2010 4:07 pm

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

Postby keme » Mon Aug 16, 2010 4:20 pm

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.
OOo 3.3.0 and Apache OOo 3.4.1/4.0.0, on Ms Windows 7 and Mac OS-X.
User avatar
keme
Volunteer
 
Posts: 1601
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

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

Postby Leiif » Mon Aug 16, 2010 9:34 pm

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

Postby keme » Mon Aug 16, 2010 10:21 pm

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.
OOo 3.3.0 and Apache OOo 3.4.1/4.0.0, on Ms Windows 7 and Mac OS-X.
User avatar
keme
Volunteer
 
Posts: 1601
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

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

Postby Richarda44 » Mon Aug 16, 2010 10:40 pm

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 3.4.1. bld 9593
on MS Windows 7 64 SP1
Chillblast fusion i5

I'm learning to see things differently
Richarda44
 
Posts: 152
Joined: Sat May 24, 2008 6:59 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 12 guests