Adding time in one cell showing results in different cell

[steph8729]

HI
I am very very very beginner when it comes to excel. I am being asked to add the time in lets say A1 and show the results in A2.

Example:
A1: 8am-5pm A2: 9

i know it would be easier to put 8 in A1 5 in A2 and show the sum in A3 but they do not want it that way. I am wondering if there is a formula to have it automatically add the time from cell A1 and have the results in A2. Im not too good when it comes to excel so i do not know all the terminology or steps to get there so if there is a way, simple basic steps would be greatly appreciated (like excel for dummies simple). i know there are many other post about this i just can't seem to figure it out :/ Thank you so much in advance!

 

[HalfAce]

Hi Steph, welcome to the board.
Am I correct in thinking you've been asked to input (for example) 8AM-5PM into one cell and show the result of how many hours elapsed between the two times in another cell?
Typically we would use 2 cells for the (start & end) times and a third cell for the calculation. As it's written, the cell containing the value '8AM-5PM' would be a text value, not a numeric value that excel can do math on.

Are you sure this is what's being asked of you? If it is actually what they're asking, is there any way you can get 'them' to show you an example of how to do it? (My bet is they can't.)

 

[TKB]

Well, i might have figured out a way, but it will only work if the contents of A1 dont vary too much:

I am going to place all of the formulas on the same sheet, but they can be moved to wherever you like as long as they properly reference the correct cell (if you dont want the calculations visible) This also assumes that each set of times will be a morning time and an afternoon time.

	A	B	C	D	E	F
1	initial Time	Morning Time	Afternoon Time	Morning Value	Afternoon Value	Total Time
2	8am-5pm	8am	5pm	8	17	9

<tbody>
</tbody>

Formulas start in B2

B2 =LEFT(A2,FIND("-",A2)-1)

C2 =RIGHT(A2,FIND("-",A2)-1)

D2 =LEFT(B2,FIND("am",B2)-1)

E2 =(LEFT(C2,FIND("pm",C2)-1))+12

To make this an afternoon time, i added 12 hours, so instead of outputting 5, it will output 17 (5+12)

F2 =E2 - D2


I hope this works, or that someone with more knowledge of Macro's can help you out.

 

[steph8729]

i dont think it necessarily needs to say am or pm as long as the results in the second cell show the correct answer like if A1: 12-6 then A2: 6

so in the end it would look like

shift hrs
12-6 6
8-5 9
where the hrs are automatically calculated. its an assignment for work apparently the person in the position before me created a timesheet this way that they absolutely loved and showed it in this format and somehow they "misplaced it" but i suggested doing it in 2 different cells and it was like i was speaking gibberish. I really didnt think it was possible but they insisted that it is so i figured i would check this site out. i have found it for simply adding 2 numbers in a cell but not sure how it works when changing it to time.

 

[T. Valko]

i dont think it necessarily needs to say am or pm as long as the results in the second cell show the correct answer like if A1: 12-6 then A2: 6

so in the end it would look like

shift hrs
12-6 6
8-5 9
where the hrs are automatically calculated. its an assignment for work apparently the person in the position before me created a timesheet this way that they absolutely loved and showed it in this format and somehow they "misplaced it" but i suggested doing it in 2 different cells and it was like i was speaking gibberish. I really didnt think it was possible but they insisted that it is so i figured i would check this site out. i have found it for simply adding 2 numbers in a cell but not sure how it works when changing it to time.

It can be done but it will be much more complicated than it should have to be (KISS ).

The first thing we have to do is make sure there is a consistent format in which these time entries are made. So, either use the AM/PM in every cell or don't use it AT ALL.

Will any times span past midnight like: 7pm-3am or 4pm-12am ? Do any times contain minutes like: 7:30am-3:30pm ?

How about posting SEVERAL examples so we can see what we're dealing with.

 

[TKB]

Well, if they like seeing it displayed as 8am-5pm, then using the formulas i stated above should achieve what your looking for, the only difference would be that all formulas in columns B, C, D, and E would be shifted over to the right 1 column, and column F would be placed in column B. This will display the total time in the column next to 8am-5pm it would look like this:

REVISED TABLE (to accommodate the total time spent during that day appearing in column B)

	A	B	C	D	E	F
1	initial Time	Total Time	Morning Time	Afternoon Time	Morning Value	Afternoon Value
2	8am-5pm	9	8am	5pm	8	17

<tbody>
</tbody>

Formulas start in B2

B2 =E2 - D2

C2 =LEFT(A2,FIND("-",A2)-1)

D2 =RIGHT(A2,FIND("-",A2)-1)

E2 =LEFT(B2,FIND("am",B2)-1)

F2 =(LEFT(C2,FIND("pm",C2)-1))+12

To make this an afternoon time, i added 12 hours, so instead of outputting 5, it will output 17 (5+12)


Otherwise, please show an example using a table of exactly what they want to see and i will try to mimic it with the correct formulas.

 

[steph8729]

oh ok sorry its suppose to look like this

Sunday Monday Tuesday
shift hrs shift hrs shift hrs
employee1 12-6 6 5-9 4 10-1 3
employee2 8-5 9 8-5 8 3-7 4
employee3 5-9 4 12-7 7 5-9 4



its only going to be between the hrs of 8am and 9pm.

 

[TKB]

could you please create a table under advanced reply and show what your looking for. Thanks!

 

[steph8729]

ok im sorry here is a table of what it will look like

sunday		monday		tuesday
shift	hrs	shift	hrs	shift	hrs
12-6	6	5-9	4	10-1	3
8-5	9	8-5	9	3-7	7
5-9	4	12-7	7	5-9	5

<tbody>
</tbody>

its only going to be between the hrs of 8am and 9pm.

 

[TKB]

Ive tried a few times to get it to work in a macro, and i cant seem to get it to work. The only thing i can think of is to create another sheet to run the calculations i stated above on and then just have the hrs column output the results.