Start time added with manual hours = ending time

randaza1

New Member
Joined
Mar 4, 2008
Messages
48
Office Version
  1. 365
Platform
  1. Windows
Hello everyone...
I am trying everything and getting a tad bit no where.
I am trying to setup a START and END time.

Here is a brief example:
I enter in a start time in A1 (we will say 9:30 am)
I want to find out what time it will be when i manually enter given amount of total of time, so I enter into B1 the number 1.5 (1 hour and 30 minutes)
as you guessed it, C1 is where I want the new time to show, in this case I am expecting C1 to report back 11:00 am

I kind of understand about the =SUM(A1)+TIME(1,30,0) where the 1 & 30 will add 1:30 minutes so C1 would report back 10:00 am.

But the only info I have is basically A1+B1 I am trying to solve for C1
Thanks

Tony
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Hi,

Based only on what you described, format C1 as Time:


Book1
ABC
19:30 AM1.511:00:00 AM
29:30 AM0.7510:15:00 AM
39:30 AM2.2511:45:00 AM
Sheet21
Cell Formulas
RangeFormula
C1=A1+(B1/24)
 
Upvote 0
Well, you have to realise how all the Excel time maths is done. In A1, you have 9:30 AM, which is actually an Excel Serial Number with a value here of 0.395833333333333. Select A1 and change the cell format to General to witness it for yourself. The value 0.39583 is the portion of 24 hours that the time 9:30 AM represents. Therefore if you wish to add the value 1.5 hours to an Excel Time Serial Number, you must first divide the increment by 24. So, in C1 put this: =A1+B1/24

If you have some spare time, watch this video re Excel Time Maths. https://www.youtube.com/watch?v=5jnQCQAzDWo&t=3s
 
Last edited:
Upvote 0
Hi,

Based only on what you described, format C1 as Time:

ABC
19:30 AM1.511:00:00 AM
29:30 AM0.7510:15:00 AM
39:30 AM2.2511:45:00 AM

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet21

Worksheet Formulas
CellFormula
C1=A1+(B1/24)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
Thank you so much..Yep that works.
My actual function was =SUM(A1+B1), but if I used the following, it came up as 9:30:00 PM
A1 = 9:30 AM
B1 = 1.5
C1 = 9:30:00 PM

So the trick is to /24 (24 hours in a day)
But again...its working with your correction..

Tony
 
Upvote 0
You're welcome, the brackets in my formula are not required, as DRSteele shown in Post #3 will work the same, it's just a habit of mine.

BTW, for future reference, if you use the SUM function, you don't need the + sign (e.g. SUM(A1,B1)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

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