Excel help

10
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.

  1. Ask
Groups
  1. Ask
10 Comments

You don't need anything complex or tricky. This would work - imgur.com/a/A…em4

Original Poster

dxx3 m ago

You don't need anything complex or tricky. This would work - …You don't need anything complex or tricky. This would work - https://imgur.com/a/AHem4


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

Sounds like you need to redesign it to allow for shifts as you will be splitting the shift across two days. I think you need to set additional columns for shift pattern and overtime.

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.

MIDURIX16 m ago

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



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/

MIDURIX17 m ago

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


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.

This will work -

=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.

Lazy solution add a new column or incorporate it into a formula. If C12<B12 add 24 else keep the same. Do the maths on new column.

Hi. Are any of the solutions above working for you? I am puzzled by the description of your problem. You say that you are entering times, but your COVERT function manipulates an amount of days.

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)

exceljet.net/for…ime

=IF(end>start, end-start, 1-start+end)
Where "end" C and "Start" is B on your columns.
=If(C12>B12,C12-B12,1-B12+C12)
Format return value column custom number with "[h]:mm" to display elapsed hours,minutes.
Edited by: "cecilmcroberts" 8th Nov 2017

Original Poster

sean85661 h, 26 m ago

Hi. Are any of the solutions above working for you? I am puzzled by the …Hi. Are any of the solutions above working for you? I am puzzled by the description of your problem. You say that you are entering times, but your COVERT function manipulates an amount of days. 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)


Haven'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.
Post a comment
Avatar
@
    Text