excel formula help

Magic_x_uk · 17 Apr 2016 at 10:55

Hi, cannot seem to get my head round the following. perhaps some one can help with the formula for Excel.

here is what i want

basically. in what cell time you arrive at work. another cell time you leave work. then a way of adding those cells up.

eg:
A1........................B1................C1
Start Time........Finish Time.....Total Hours
8.30am...............3.30pm...........7 hours

Thank you people

[FnG]magnolia · 17 Apr 2016 at 11:03

By 'adding those cells up', do you mean calculating the difference between the two times?

joeyjojo · 17 Apr 2016 at 11:20

[FnG]magnolia;29399925 said:
By 'adding those cells up', do you mean calculating the difference between the two times?

C2 = B2 - A2

james.miller · 17 Apr 2016 at 11:27

I'm not saying this is the neatest way but this will convert the text time in cell A1 to a decimal time:

Code:

=(LEFT(A1,FIND(".",A1)-1) + IFERROR(IF(FIND("pm",A1)>0,12,0),0))+(MID(A1,FIND(".",A1)+1,2))/60

just copy that to cell d1 (which will than covert the text time in cell b1) then d1-c1 gives you your total hours. Will work providing the times are entered as x.xx[am/pm]

eg:

crapler8 · 17 Apr 2016 at 15:27

Not sure how thats gonna work

james.miller · 17 Apr 2016 at 16:35

It does work, have you tried it?

Sheff · 17 Apr 2016 at 16:48

If you're entering these times manually you could simply use a time format that Excel recognizes (such as hh:mm) then use the formula joeyjojo gave.

Guest2 · 17 Apr 2016 at 17:16

I think you need something like this - http://www.officetooltips.com/excel/tips/calculating_the_difference_between_two_time_stamps.html

Gixer · 17 Apr 2016 at 17:20

Just set the format as time HH:MM:SS (under the time category) for the input boxes and [H]:mm:ss for the output box and do the equation Joey said.

Job done if you want the results in hours and minutes.