If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 Trying to work out a formula for time lost/gained
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Trying to work out a formula for time lost/gained

#11
August 16th 12, 04:32 PM posted to microsoft.public.excel.newusers
 joeu2004[_2_] external usenet poster Posts: 637
Trying to work out a formula for time lost/gained

"Laurr456" > wrote:
> I have managed to get the credit/debit part of my
> sheet working, now i'm just bamboozled by the running
> balance part..as it starts with a minus figure.
> I need to know how to make it so when I have a credit
> the balance goes down and a debit makes the balance
> go up..?

Referring to http://www.excelbanter.com/attachmen...tachmentid=541,
which you mention in another posting in this thread....

That will be easy. But first, you need to tell us:

1. What are the units of -12.00? Hours? The values in the credit and debit
are Excel times (hh:mm). But -12.00 is a decimal number.

2. Should the -12.00 be on a line by itself before the first line of data.
That is, after line 2, but before line 3? Otherwise, I can we subtract/add
the first credit/debit?

Assuming -12.00 represents hours, it is in J3, and the data starts in row 4,
the formula in J4 might be:

=J3 + IF(H4<>"",H4*24,IF(I4<>"",-I4*24,0))

Some other observations:

3. The formula in G3 is =SUM(F3-D3-E3). There is no point in using the SUM
function there. Simply write =F3-D3-E3.

However, I think it would be prudent to write =--TEXT(F3-D3-E3,"hh:mm"),
since you compare values in column G with "constant" times of the form
TIME(7,24,0).

The double-negative (--) converts text to numeric time. The TEXT function
effectively rounds to the minute. It is necessary to eliminate arithmetic
anomalies that creep into Excel computations. These cause infinitesimal
differences with equivalent constants. So theoretically at least, it is
possible for G3 to display as 7:24, but G3>TIME(7,24,0) or G3<TIME(7,24,0)
might be true.

4. I would write TIME(7,24,0) instead of TIME(7,24,). I know: the two are
equivalent. But IMHO, it is error-prone to omit seconds in that manner. In
fact, it seems to be an accident of implementation that the omission is
interpreted as zero. I do not see that allowed in the TIME help page. I

#12
August 16th 12, 04:42 PM posted to microsoft.public.excel.newusers
 joeu2004[_2_] external usenet poster Posts: 637
Trying to work out a formula for time lost/gained

Errata.... I wrote:
> Assuming -12.00 represents hours, it is in J3, and the
> data starts in row 4, the formula in J4 might be: =J3 +
> IF(H4<>"",H4*24,IF(I4<>"",-I4*24,0))

Just notice that you have a "break" -- a blank row between weeks. To make
it easy to copy the formula down, do the following:

=IF(G4="","",IF(J3="",J2,J3)+IF(H4<>"",H4*24,IF(I4 <>"",-I4*24,0)))

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post lost work on xl idiot Excel Discussion (Misc queries) 2 October 31st 06 07:27 PM How to write the formula for time accounting for a work schedule [email protected] Excel Worksheet Functions 1 December 20th 05 10:35 AM Formula for computing work time in Excel Alex Vinokur New Users to Excel 3 September 20th 05 06:58 PM How can i work a formula for time limit? Roze Excel Worksheet Functions 2 November 25th 04 02:41 PM Knowledge gained from Access to Excel VBA Automation User request ImraneA Excel Programming 0 January 29th 04 04:35 PM

All times are GMT +1. The time now is 02:44 PM.