# Excel help

Found 8th Nov 2017

I've made an hours worked table and in the table are a column of start times and a column of finish times. I'm using

=CONVERT(C12-B12, "day", "hr")

to get the total hours worked from the two columns, but if someone were to start work at say 8pm (20:00) and then finish at 6am (6:00) it gives me a negative value. I need a positive value please.I know you can use =ABS for things like this, but not too sure what I'm doing.

Thanks in advance.

=CONVERT(C12-B12, "day", "hr")

to get the total hours worked from the two columns, but if someone were to start work at say 8pm (20:00) and then finish at 6am (6:00) it gives me a negative value. I need a positive value please.I know you can use =ABS for things like this, but not too sure what I'm doing.

Thanks in advance.

Thanks, but I can't have start day columns. It is just a start time and a finish time. Maybe its not possible.

Remember you only get paid standard salary for your shift pattern unless it's overtime in which any additional hours get multiplied by your overtime rate.

It's possible but you need to tell the computer it's between two days. For example your night shift started monday 8pm and finished tuesdays 6am.

ablebits.com/off…el/

Why can't you have start and end day columns?

An alternative, albeit one which would require some slightly awkward formula, would be an If in which if your end time is before your start time, 1 is added to the date. Excel stores date&time as a number and a fraction (eg. 12 noon on the 8th Nov 2017 is 43048.5), so you'd basically be working to exploit that.

=IF(CONVERT(C2-B2, "day", "hr")<0,CONVERT(C2-B2, "day", "hr")+24,CONVERT(C2-B2, "day", "hr"))

May be a neater way of doing it but gives the correct answer.

I am with Oneday77 on this one. If you were to add values like 0800, 1600, 2000 for the times in B12 and C12, and had the B12 and C12 columns formatted for numbers, then this If function would give you the amount of hours worked each time.

=IF(C12-B12>0, (C12-B12)/100, (C12-B12+24)/100)

Edited by:"cecilmcroberts" 8th Nov 2017Haven't had chance to turn the laptop on. I'm just manipulating an existing document who's format absolutely can not change, I just want to make it do all the calculations for everyone. I used the convert formula because what I was using previously was giving me it in rounded hours, or hours and minutes, but I just want hours translated to decimal.

Thanks everyone for the info, I will have a look at it when I get a chance.