Home 
Search 
Today's Posts 
#1




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




Quote:
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. 
#3




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




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,),A2TIME(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




Wow, thank you both so much  problem solved!!

#6




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




Quote:

#8




Here's a copy of my sheet so far
Thanks 
#9




Quote:
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




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..? 
Reply 

Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
lost work on xl  Excel Discussion (Misc queries)  
How to write the formula for time accounting for a work schedule  Excel Worksheet Functions  
Formula for computing work time in Excel  New Users to Excel  
How can i work a formula for time limit?  Excel Worksheet Functions  
Knowledge gained from Access to Excel VBA Automation User request  Excel Programming 