Apache OpenOffice Community Forum

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

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.

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

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.

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)