time calculations in excel

[caLLous]

Hi

I'm trying to do a costed rota for work but the time calcs are doing my head in. I've never been able to do these successfully before and grrr...

I need it to work out hours worked from a start time and an end time. When I change the cell type to Time it puts seconds on the end which really isn't necessary.

Is there an easy way of doing this and am I being a bit stoopid?

 

[dysfunction]

You can just do a custom format like so:

h:mm;@

or something similar

 

[Dukat]

If you're doing it to find hours worked per day I would do it like this:

Have a cell, say A1, to have the start hour in, and another, say A2, for the end hour. Put the values in as whole numbers, 1 or 2 digits, in a 24hour time style - keep the cells set to the default format though, dont use the "time" format because it will just cause complications (imo)

So for example you might have:

A1= 9
A2= 17 (meaning 5pm in 24 hour time)

Then put the following code in the cell that will calculate and display the result (say A3): "=A2-A1"

A3 will then display the result, which will be the total hours worked that day(8).

 

[Jupitus]

Just type the start and end times in cells 1 and 2, then type the formula in cell 3 as '=cell2-cell1' (i.e. '=A2-A1'). Next, highlight the cells in question and select 'format', 'cells', 'custom' and in the entry box type 'h:mm' and hit enter.

Should work...

 

[Dukat]

(i.e. '=A2-A1').

Shamelessly plagiarized!

I demand credit when you use my so original ideas

 

[TdC]

so ni your formulae, what happens when the user goes through midnight? overtime factoring? eh? eh? all teh fail!!1

 

[Dukat]

so ni your formulae, what happens when the user goes through midnight? overtime factoring? eh? eh? all teh fail!!1

Or you could just add 24 to the end time...


EXCELOWNED!!!11111oneoneoen

EDIT: or if you're really clever, put a button in for "working past midnight" that will do it for you rar!

Something like:

If (PastMidnight = false) then
A3=A3+24
PastMidnight=true
Else
A3=A3-24
PastMidnight=false
End if

Behind a button should toggle it on and off for you.

 

[Scouse]

EXCELOWNED!!!11111oneoneoen

I tried to explain to someone who knows nothing about all this guff but she'll never get it. Ever.

 

[TdC]

I bow to your superior knowhow Dukat. Excel may be tres ghei, but at least it's not Lotus Notes

 

[Jupitus]

Custom format start and stop time cells to include a date, ie 'format', 'cells', 'custom' and enter 'dd/mm/yyyy hh:mm'

Then you enter the date aswell in the start and stop times:

1/4/2006 17:00

2/4/2006 04:00

Enter the '=A2-A1' formula in a cell and custom format that to 'hh:mm' and you will have hours worked...

BUT

Reading all this again, I think Callous' issue is that the real value in the resultant cell will not be a number of hours, it will be a time representation i.e. '15:00' will actually have a value of .625 as Excel calculates using day units and 15/24 = 0.625.

If you need to know number of hours worked in order to be able to, for example, multiply this by an hourly salary you need to multiply the number of hours by 24.

Here is how to do that:

Cell A1 = start date and time.
Cell A2 = end date and time.

Highlight these 2 cells and custom format as 'dd/mm/yyyy hh:mm' as described above.

Format Cell A3 just as a normal number, then enter the formula '=(A2-A1)*24'

This will result in the number of hours between date and time in A1 and date and time in A2. You can use this number in calculations just as it is.

 

[caLLous]

so ni your formulae, what happens when the user goes through midnight? overtime factoring? eh? eh? all teh fail!!1

Exactly, seeing as this is a nightclub pretty much every employee will work from xpm on one day to xam on the next.

Thanks to everybody, I'll have a play when I get home (because I should obviously have to do stuff like this outside work ).

 

[Jupitus]

Cal - was it to work out amounts to be paid as I guessed?

I can knock up a template for you and send you a d/l link tomorrow if I get a chance...

 

[caLLous]

Yeah that's it Jup. I was a bit rushed with the replies at work because I had other stuff to do but I'm going to have a tinker now and we'll see how it turns out.

	a	b	c	d	e	
1	start	end	hours	rate	total
2	19.00	21.00	2.00	£5.00	£10.00

The formula in C2 is =IF(B2-A2<0,(B2+24)-A2,B2-A2) and E2 is =(C2*D2)*24. This works fine for same-day hours, but as soon as I bump the end time past midnight, it freaks out:

	a	b	c	d	e	
1	start	end	hours	rate	total
2	19.00	01.30	6.30	£5.00	£2792.50

Any ideas?

 

[caLLous]

	a	b	c	d	e	
1	start	end	hours	rate	total
2	23.30	00.30	1.00	£1.00	£553.00

wtf does have £553 have to do with the price of cheese (or bar staff, in this case)

 

[pcg79]

enjoy.

oh and the problem was that it was counting the hours wrong. the +24 thing makes it go a bit mental. you can see what ive done in my spreadsheet.

 

[caLLous]

Thanks pcg, although for some reason your formula didn't work when I put it into the timesheet I'd already done (see attachment). I'm not saying it doesn't work, it blatantly does in your example, but I had to play around with it a bit to get it working.

View attachment costed_rota_24.11.06_blank.xls

 

[Jupitus]

pm me your email addy call

 

[caLLous]

done

 

[Jupitus]

reply sent