Adding times

B

Brutium

Board Regular
Joined
Mar 27, 2009
Messages
188
Hello everyone,
I am trying to help my wife with a schedule she is making, but all attempts at adding times have gone the wrong way.
Let me explain what I need to do.

Let say that in cell A3 I have a start time of the format e.g. 10:30, and to this I would like to add 3.5 hours. What I need to accomplish, is to get in cell C3 the time 14:00.

How do I accomplish this?

Tank you in advance for any help you can give me.
 
Sort by date Sort by votes
Brutium said:
I am entering the number 30, NOT the string. What I expect is the number of hours and minutes that all the 30 minutes add up to. In a different cell I am collecting these values so that I may add them up together with the number of hours worked so that my wife can be paid not only for the work time but also the travel time. So if she works 6 hours in a day and she has a 2:30 travel time, the company will have to pay her 8:30 hours all together.
Click to expand...
OK, try this...

=SUM(E6:E11)/60

That will give the result as a decimal value, for example, 2.

If you want the result as a time value, for example, 2:00, then use this version:

=SUM(E6:E11)/1440

Format the cell as [h]:mm.
 
Upvote 0

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Thank you T.Valko, I used the =SUM(E6:E11)/60 formula and it works just fine. The only issue is that when I add up the number of hours she works, and the travel time, the result is e.g. 13.50 hours instead of 13:30. This is a minor glitch which I can live with, but I would like to figure what formula I could use to get either values.
Again...

THANK YOU!!!!!
 
Upvote 0
Brutium said:
Thank you T.Valko, I used the =SUM(E6:E11)/60 formula and it works just fine. The only issue is that when I add up the number of hours she works, and the travel time, the result is e.g. 13.50 hours instead of 13:30. This is a minor glitch which I can live with, but I would like to figure what formula I could use to get either values.
Again...

THANK YOU!!!!!
Click to expand...
Show me what the formula is that returns 13.50.
 
Upvote 0
T. Valko said:
Show me what the formula is that returns 13.50.
Click to expand...

[TABLE="width: 151"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="width: 73, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, width: 73, bgcolor: yellow"]Total Hrs. worked[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]10.50[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, width: 73, bgcolor: yellow"]Total Mins. Trav. Time[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]3.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: #ffc000"]Day Total[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]13.50[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]
As you see from the chart, in G12 I have Total Hrs. worked and in H12 I have the sum of the hours my wife worked in a day, in G13 I have the Total minutes of Travel Time and this is expressed in hours. In G14 Day Total is reported in H14 (13.50) the formula I have is =SUM(H12:H13) or the sum of 10.50 + 3.00 therefore 13.50 This is the table I am using:

[TABLE="width: 481"]
<TBODY>[TR]
[TD][/TD]
[TD]A</SPAN>[/TD]
[TD]B</SPAN>[/TD]
[TD]C</SPAN>[/TD]
[TD]D</SPAN>[/TD]
[TD]E</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Hrs Work</SPAN>[/TD]
[TD]Time in</SPAN>[/TD]
[TD]Time out</SPAN>[/TD]
[TD]Client</SPAN>[/TD]
[TD="colspan: 2"]Trav. Time</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]1.00</SPAN>[/TD]
[TD="align: right"]8:30</SPAN>[/TD]
[TD="align: right"]9:30</SPAN>[/TD]
[TD] Fred[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]2.00</SPAN>[/TD]
[TD="align: right"]10:00</SPAN>[/TD]
[TD="align: right"]12:00</SPAN>[/TD]
[TD] Frank[/TD]
[TD="align: right"]30</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]2.50</SPAN>[/TD]
[TD="align: right"]12:30</SPAN>[/TD]
[TD="align: right"]15:00</SPAN>[/TD]
[TD] Rose[/TD]
[TD="align: right"]30</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]1.00</SPAN>[/TD]
[TD="align: right"]15:30</SPAN>[/TD]
[TD="align: right"]16:30</SPAN>[/TD]
[TD] Manny[/TD]
[TD="align: right"]30</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]2.00</SPAN>[/TD]
[TD="align: right"]17:00</SPAN>[/TD]
[TD="align: right"]19:00</SPAN>[/TD]
[TD] Dom[/TD]
[TD="align: right"]30</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]1.00</SPAN>[/TD]
[TD="align: right"]19:30</SPAN>[/TD]
[TD="align: right"]20:30</SPAN>[/TD]
[TD] Mary[/TD]
[TD="align: right"]30</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]1.00</SPAN>[/TD]
[TD="align: right"]21:00</SPAN>[/TD]
[TD="align: right"]22:00</SPAN>[/TD]
[TD] George[/TD]
[TD="align: right"]30</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL span=2><COL><COL span=2></COLGROUP>[/TABLE]
In Column A5:A11 I placed the hours as a Number, while in B5:B11 the values are Time, and that is the reason for B7 2.5, representing 2.5 hours where as the times are 12:30, 15:30 etc.
 
Upvote 0
YES, YES, YES!!!!

It works great, Thank you.

But how can I NOT show "Time In" values if I do not have any Travel Time? at the moment I am using on a second sheet (Sheet2) the formula "=Sheet1!C5+IF(Sheet1!E6="",0,TIME(0,30,0))" naturally without the quotation marks. If I do not have any entries in, let say E9:E11 the table still shows values in B9:B11 since it is taking the values from Sheet2.
This is what my table looks like:

[TABLE="width: 483"]
<TBODY>[TR]
[TD][/TD]
[TD]A</SPAN>[/TD]
[TD]B</SPAN>[/TD]
[TD]C</SPAN>[/TD]
[TD]D</SPAN>[/TD]
[TD]E</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Hrs Work</SPAN>[/TD]
[TD]Time in</SPAN>[/TD]
[TD]Time out</SPAN>[/TD]
[TD]Client</SPAN>[/TD]
[TD="colspan: 2"]Trav. Time</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]6</SPAN>[/TD]
[TD="align: right"]3.00</SPAN>[/TD]
[TD="align: right"]8:00</SPAN>[/TD]
[TD="align: right"]11:00</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7</SPAN>[/TD]
[TD="align: right"]1.00</SPAN>[/TD]
[TD="align: right"]11:30</SPAN>[/TD]
[TD="align: right"]12:30</SPAN>[/TD]
[TD] [/TD]
[TD="align: right"]30</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8</SPAN>[/TD]
[TD="align: right"]2.50</SPAN>[/TD]
[TD="align: right"]13:00</SPAN>[/TD]
[TD="align: right"]15:30</SPAN>[/TD]
[TD] [/TD]
[TD="align: right"]30</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9</SPAN>[/TD]
[TD="align: right"]1.00</SPAN>[/TD]
[TD="align: right"]16:00</SPAN>[/TD]
[TD="align: right"]17:00</SPAN>[/TD]
[TD] [/TD]
[TD="align: right"]30</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10</SPAN>[/TD]
[TD] [/TD]
[TD="align: right"]17:00</SPAN>[/TD]
[TD="align: right"]17:00</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11</SPAN>[/TD]
[TD] [/TD]
[TD="align: right"]17:00</SPAN>[/TD]
[TD="align: right"]17:00</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12</SPAN>[/TD]
[TD] [/TD]
[TD="align: right"]17:00</SPAN>[/TD]
[TD="align: right"]17:00</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL span=2><COL><COL span=2></COLGROUP>[/TABLE]
I
I would like to make sure that the 17:00 times in B10:B12 show empty.
Is it possible?
 
Upvote 0
Brutium said:
YES, YES, YES!!!!

It works great, Thank you.

But how can I NOT show "Time In" values if I do not have any Travel Time? at the moment I am using on a second sheet (Sheet2) the formula "=Sheet1!C5+IF(Sheet1!E6="",0,TIME(0,30,0))" naturally without the quotation marks. If I do not have any entries in, let say E9:E11 the table still shows values in B9:B11 since it is taking the values from Sheet2.
This is what my table looks like:

[TABLE="width: 483"]
<TBODY>[TR]
[TD][/TD]
[TD]A</SPAN>
[/TD]
[TD]B</SPAN>
[/TD]
[TD]C</SPAN>
[/TD]
[TD]D</SPAN>
[/TD]
[TD]E</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Hrs Work</SPAN>
[/TD]
[TD]Time in</SPAN>
[/TD]
[TD]Time out</SPAN>
[/TD]
[TD]Client</SPAN>
[/TD]
[TD="colspan: 2"]Trav. Time</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]6</SPAN>
[/TD]
[TD="align: right"]3.00</SPAN>
[/TD]
[TD="align: right"]8:00</SPAN>
[/TD]
[TD="align: right"]11:00</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7</SPAN>
[/TD]
[TD="align: right"]1.00</SPAN>
[/TD]
[TD="align: right"]11:30</SPAN>
[/TD]
[TD="align: right"]12:30</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]30</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8</SPAN>
[/TD]
[TD="align: right"]2.50</SPAN>
[/TD]
[TD="align: right"]13:00</SPAN>
[/TD]
[TD="align: right"]15:30</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]30</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9</SPAN>
[/TD]
[TD="align: right"]1.00</SPAN>
[/TD]
[TD="align: right"]16:00</SPAN>
[/TD]
[TD="align: right"]17:00</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]30</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]17:00</SPAN>
[/TD]
[TD="align: right"]17:00</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]17:00</SPAN>
[/TD]
[TD="align: right"]17:00</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]17:00</SPAN>
[/TD]
[TD="align: right"]17:00</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
I
I would like to make sure that the 17:00 times in B10:B12 show empty.
Is it possible?
Click to expand...
Maybe this...

=IF(E9="","",Sheet1!C5+IF(Sheet1!E6="",0,TIME(0,30,0)))
 
Upvote 0
Hello T.Valko,

Thank you for your help, I really appreciate it.
I modified the formula you suggested to accommodate the fact that this should go on Sheet 2 of my program.
=IF(Sheet1!E6:E11="","",Sheet1!C5+IF(Sheet1!E6="",0,TIME(0,30,0)))


Unfortunately I get these errors:
[TABLE="width: 413"]
<TBODY>[TR]
[TD] [/TD]
[TD]A</SPAN>[/TD]
[TD]B</SPAN>[/TD]
[TD]C</SPAN>[/TD]
[TD]D</SPAN>[/TD]
[TD]E</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Hrs Work</SPAN>[/TD]
[TD]Time in</SPAN>[/TD]
[TD]Time out</SPAN>[/TD]
[TD]Client</SPAN>[/TD]
[TD]Trav. Time</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5</SPAN>[/TD]
[TD="align: right"]4.00</SPAN>[/TD]
[TD="align: right"]8:30</SPAN>[/TD]
[TD="align: right"]12:30</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]6</SPAN>[/TD]
[TD] [/TD]
[TD]#VALUE!</SPAN>[/TD]
[TD]#VALUE!</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7</SPAN>[/TD]
[TD] [/TD]
[TD]#VALUE!</SPAN>[/TD]
[TD]#VALUE!</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]8</SPAN>[/TD]
[TD] [/TD]
[TD]#VALUE!</SPAN>[/TD]
[TD]#VALUE!</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]9</SPAN>[/TD]
[TD] [/TD]
[TD]#VALUE!</SPAN>[/TD]
[TD]#VALUE!</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]10</SPAN>[/TD]
[TD] [/TD]
[TD]#VALUE!</SPAN>[/TD]
[TD]#VALUE!</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]11</SPAN>[/TD]
[TD] [/TD]
[TD]#VALUE!</SPAN>[/TD]
[TD]#VALUE!</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL span=2><COL><COL></COLGROUP>[/TABLE]
How can I get rid of the #VALUE! errors to appear?
 
Upvote 0
T.Valko,

Thank you for your help. I think I solved my dilemma. I modified the formula I had in the various cells in column C, by placing an IFERROR in front of my formula in the cells. So the formula now reads:
=IFERROR((B11+A11/24),"")
With this formula, I will do all the calculations, but if there is an error, it will return a blank cell.

Once again, THANK YOU for all your help and for "forcing" me to think....

Brutium
 
Upvote 0
Brutium said:
T.Valko,

Thank you for your help. I think I solved my dilemma. I modified the formula I had in the various cells in column C, by placing an IFERROR in front of my formula in the cells. So the formula now reads:
=IFERROR((B11+A11/24),"")
With this formula, I will do all the calculations, but if there is an error, it will return a blank cell.

Once again, THANK YOU for all your help and for "forcing" me to think....

Brutium
Click to expand...
Good deal. Thanks for the feedback! :cool:
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Time difference (conditional format)
Replies
3
Views
239
JamesCanale
JamesCanale
V
  • Locked
  • Question Question
If statement formula help between two times
Replies
2
Views
228
Fluff
Fluff
R
  • Solved
Adding Hours & Minutes to Dates & Times w/in Working Hours
2
Replies
11
Views
568
Asbestos_Jen
Asbestos_Jen
R
  • Solved
Formatting time - minutes and seconds NOT time of day
Replies
2
Views
631
RodneyW
R
JeffGrant
  • Question Question
Application.Ontime misses scheduled time
2 3
Replies
24
Views
944
offthelip
O

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top