I've been trying to get excel to tell me the amount of time between (i.e.) 20:00:00 and say 03:00:00 and I can't seem to figure it out. I'm able to get it to tell me from the 0 hour to say 03:34:00 = 3hrs. & 34mins. I would like to display different times in column B and show me the total time in column C. By key stroking crtl+shift+; the current time is displayed in B. The formula I have in column C right now is =SUM(B2-B3+C2). Can anyone help me with this. I'm using this to track time on a project.
Also, in column D I'm seeking it to display a total if the time in column C begins to be greater than 8 hours.
Solutions anyone?
Thanks,
Thomas
Formulas: Time Switch Calculation ???
-
-
-
Hi Thomas,
First part of the question - calculating difference between times . . .
Example
B1 = 20:00:00
C1 = 03:00:00
D1 =IF(C1 < B1, C1+24-B1, C1-B1)
will give the answer 07:00:00make sure all cells are formated hh:mm:ss
Didn't really understand the other part of your question...maybe posting a sample sheet would help.
.....Ralph
-
Hi, Ralph
I've attached the spreedsheet I'm working on for viewing. I tried your formula, but couldn't make it work. I'm not very skilled at excel.
As you will see in the sheet, column B is the key stroke column, column C is supposed to keep a total of hours worked and column D is the overtime worked in one day. IF over 8 hrs, then display the difference between the actual time and 8hrs.
I'm very grateful for your help.
Thomas -
Hi Thomas,
See if the attached is something like you had in mind. I only copied the formulas down about 20 rows in Col C & D to save space on the upload.
The cells use the special "elapsed time format" = [hh]:mm:ss;@ in Col B,C,D.
I modified the original formula I posted after looking at your app.
Hope this helps
.....Ralph
-
Ralph,
Thank you so much. I never would have figured out how to do that. I've been scratching my head for weeks on that problem. Now all I need is for Excel to look at the total time in overtime column and covert it into a number that isn't 0 - 0.999999, but I can find the overtime costs manually.
Thanks so much again,
Thomas:tumble:
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!