Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 5
Red face Trying to work out a formula for time lost/gained

Hello, I'm not a complete novice when it comes to excel but I dont have much experience with anything other than simple formulas...

This is a bit tricky to explain but i'll try my best...

I'm trying to devise a table to highlight time lost or gained in a single day.

1. In the first column I want the amount of time worked (in hours and minutes)
2. In the second, if the time worked exceeds 7hrs 24mins then I would like the aditional time to show (e.g if worked 8hrs 34mins I would like 1hr 10mins to show, preferably as 1:10)
3. In the third, if the time worked is less than 7hrs 24mins. I would like the time lost to show


Any help is appreciated as all i've managed to do it get it to show the time worked or 0 in the 2nd and 3rd colums.

TIA
  #2   Report Post  
Senior Member
 
Posts: 663
Default

Quote:
Originally Posted by Laurr456 View Post
Hello, I'm not a complete novice when it comes to excel but I dont have much experience with anything other than simple formulas...

This is a bit tricky to explain but i'll try my best...

I'm trying to devise a table to highlight time lost or gained in a single day.

1. In the first column I want the amount of time worked (in hours and minutes)
2. In the second, if the time worked exceeds 7hrs 24mins then I would like the aditional time to show (e.g if worked 8hrs 34mins I would like 1hr 10mins to show, preferably as 1:10)
3. In the third, if the time worked is less than 7hrs 24mins. I would like the time lost to show


Any help is appreciated as all i've managed to do it get it to show the time worked or 0 in the 2nd and 3rd colums.

TIA
Hi,

Have a look at the attached. Is this what you mean?

I've added 7:24 to a cell and referenced this in the formulas. That way if you ever find yourself with a different trigger time (i.e. you start working longer or shorter shifts) you only have to change the time in that one cell rather than all the formulas.

Let me know if any of it needs explaining further.

S.
Attached Files
File Type: zip Laurr456 Example.zip (8.2 KB, 50 views)
  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,872
Default Trying to work out a formula for time lost/gained

Hi,

Am Mon, 13 Aug 2012 14:37:14 +0000 schrieb Laurr456:

1. In the first column I want the amount of time worked (in hours and
minutes)
2. In the second, if the time worked exceeds 7hrs 24mins then I would
like the aditional time to show (e.g if worked 8hrs 34mins I would like
1hr 10mins to show, preferably as 1:10)
3. In the third, if the time worked is less than 7hrs 24mins. I would
like the time lost to show


in B2:
=IF(A2<TIME(7,24,),TIME(7,24,)-A2,"")
in C2:
=IF(A2<TIME(7,24,),TIME(7,24,)-A2,"")



Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,872
Default Trying to work out a formula for time lost/gained

Hi,

Am Mon, 13 Aug 2012 21:56:11 +0200 schrieb Claus Busch:

in B2:
=IF(A2<TIME(7,24,),TIME(7,24,)-A2,"")
in C2:
=IF(A2<TIME(7,24,),TIME(7,24,)-A2,"")


sorry, I copied one formuala twice
in B2:
=IF(A2TIME(7,24,),A2-TIME(7,24,),"")
in C2:
=IF(A2<TIME(7,24,),TIME(7,24,)-A2,"")


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #5   Report Post  
Junior Member
 
Posts: 5
Talking

Wow, thank you both so much - problem solved!! :)


  #6   Report Post  
Junior Member
 
Posts: 5
Red face

Ok...now i've done that, I have another query.

I've had to add a 4th column (F) which is a running balance. The starting balance is a minus figure (-12).

Which formula can I use which will alter the balance per day, depending on whether there is a credit in the 2nd column (D) or a debit in the 3rd (E)

Hope this makes sense.

Thanks again
  #7   Report Post  
Senior Member
 
Posts: 663
Default

Quote:
Originally Posted by Laurr456 View Post
Ok...now i've done that, I have another query.

I've had to add a 4th column (F) which is a running balance. The starting balance is a minus figure (-12).

Which formula can I use which will alter the balance per day, depending on whether there is a credit in the 2nd column (D) or a debit in the 3rd (E)

Hope this makes sense.

Thanks again
Could you post an example of your sheet so far?
  #8   Report Post  
Junior Member
 
Posts: 5
Wink

Quote:
Originally Posted by Spencer101 View Post
Could you post an example of your sheet so far?
Here's a copy of my sheet so far

Thanks :)
  #9   Report Post  
Member
 
Location: Birmingham
Posts: 35
Default

Quote:
Originally Posted by Laurr456 View Post
Hello, I'm not a complete novice when it comes to excel but I dont have much experience with anything other than simple formulas...

This is a bit tricky to explain but i'll try my best...

I'm trying to devise a table to highlight time lost or gained in a single day.

1. In the first column I want the amount of time worked (in hours and minutes)
2. In the second, if the time worked exceeds 7hrs 24mins then I would like the aditional time to show (e.g if worked 8hrs 34mins I would like 1hr 10mins to show, preferably as 1:10)
3. In the third, if the time worked is less than 7hrs 24mins. I would like the time lost to show


Any help is appreciated as all i've managed to do it get it to show the time worked or 0 in the 2nd and 3rd colums.

TIA
___A_______B_________C_______
1__08:00___=A1-$C$1__07:26
2__07:00___


If you've got it laid out like that ^^^ then...

1. In Cell A1 will be the time worked
2. In an empty cell that you won't use, i.e C1 type in the standard time 07:26
3. Then in B1 put... =A1-$C$1

B1 should now say 00:34

To show where the time worked is under 07:26, then you will need to change the date system in Excel options.

Which version of Excel are you using?

The benefit to that though is that whether time worked in A1 is over OR under 07:26 it will show as either - or +
  #10   Report Post  
Junior Member
 
Posts: 5
Default

I'm using Office 2003.

Thanks for the reply - 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..?


  #11   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 829
Default 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 G3TIME(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
know it's not your "mistake".

  #12   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 829
Default 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)))

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
lost work on xl idiot Excel Discussion (Misc queries) 2 October 31st 06 08:27 PM
How to write the formula for time accounting for a work schedule [email protected] Excel Worksheet Functions 1 December 20th 05 11: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 03:41 PM
Knowledge gained from Access to Excel VBA Automation User request ImraneA Excel Programming 0 January 29th 04 05:35 PM


All times are GMT +1. The time now is 03:06 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"