Time calcs in Excel

[Col]

Dear all,

Having problems with a time calc in Excel.

I have to design a spreadsheet which will be used at various offices within
the company which needs to calculate staff hours worked in a week and also
the amount of rest from one day to the next.

So lets say a member of staff finishes work at 1100 on Monday and then
resumes work at 1300 on Tuesday, that is a rest period of 26 hours.

If I just take one from the other (1300-1100) Excel returns a value of
02:00:00 which is correct as far as Excel is concerned as I need to tell it
the date.

Herein lies the problem, I cannot expect the staff at the offices to input
each cell in the format "25/May/07 17:00" etc as it will be too time
consuming due to the numbers of staff involved..

So if they input the times in the format 13:00 is it possible via some code
to solve the problem. I've tried having the date in one cell and using
INDIRECT to join it and the time cell together without much success.

Any ideas?

Thanks very much for any advice.

Colin.

 

[Sandy Mann]

Assuming that the start date will always be in the day after the finish time
then try:

="13:00"-"11:00"+1

but obviously it would be better to use cell references rather than hard
coded times.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Stan Brown]

I have to design a spreadsheet which will be used at various offices within
the company which needs to calculate staff hours worked in a week and also
the amount of rest from one day to the next.

So lets say a member of staff finishes work at 1100 on Monday and then
resumes work at 1300 on Tuesday, that is a rest period of 26 hours.

Herein lies the problem, I cannot expect the staff at the offices to input
each cell in the format "25/May/07 17:00" etc as it will be too time
consuming due to the numbers of staff involved..

So if they input the times in the format 13:00 is it possible via some code
to solve the problem.

No, because you don't know if they started the day after they knocked
off, or two days after, or three days after, or ...

Somehow you have to get both date and time.

 

[Col]

Somehow you have to get both date and time.

Yes but if I had date and time in two different cells how can I calculate
the difference i.e.

Cell A1: 26/05/2007
CellA2: 16:00
CellA3: 27/05/2007
CellA4: 19:00

I would want the product of A3 and A4 minus A1 and A2 which should give me
27 hours.

Thanks,

Colin.

 

[Dave Peterson]

=(A3+A4)-(A1+A2)
And give it a custom format of: [hh]:mm:ss

or
=((A3+A4)-(A1+A2))*24
and make sure it's formatted as General (not time/date)

 

[sybmathics]

Col wrote:

each cell in the format "25/May/07 17:00" etc as it will be too time
consuming due to the numbers of staff involved..

Any ideas?

Thanks very much for any advice.

Colin.

Why not learn them to enter time and date using built-in shortcuts.

Press
Crtl and ;
result: current date

Press
Ctrl and :
result: current time

Press
Ctrl and ;
space
Ctrl and :
Result: curent date and itme in one cell

Next you can make any calculation you want.


greets,

Sybolt

 

[Col]

=(A3+A4)-(A1+A2)
And give it a custom format of: [hh]:mm:ss

or
=((A3+A4)-(A1+A2))*24
and make sure it's formatted as General (not time/date)

Yep that's the one! Works a treat.

Thanks very much for your help Dave, very much appreciated.

Colin.