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
[Solved] Formula to subtract a set number of hours from time
[Solved] Formula to subtract a set number of hours from time
Last edited by Hagar Delest on Sun Aug 29, 2010 4:11 pm, edited 1 time in total.
Reason: tagged [Solved].
Reason: tagged [Solved].
OpenOffice 3.2 on Windows XP sp3
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.
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
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
Best,
Leiif
OpenOffice 3.2 on Windows XP sp3
Re: I need a formula to subtract a set number of hours from
Right, you asked for "subtract" and i explained how to add. Glad that you're better at math than I am at readingLeiif 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 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
-
- Posts: 351
- Joined: Sat May 24, 2008 6:59 pm
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)
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
There's got to be a better way
And for all accountants - The change is coming