working with 24 hour times

  • Thread starter Thread starter Pete
  • Start date Start date
P

Pete

Hi there

Can anyone help me with the following problem.

I have a spreadsheet that allows people to enter times on and off from work.
The time must be recorded in 24 hour format. From this, calculations are
made of the numbers of people at work at any one time.

I have not been able to get this to work with time format and the only
(crude) work around I have managed is to use the numbers 1 to 24 to
represent the times. As a result of this I have only got it to work with
full hours and I now need it to work more accurately.

An example of what I am trying to achieve is: Time on: 18:30 Time off:
24:00. I had intended to use a time format but cannot get it to record
midnihght properly as I cannot record 24:00 as it defaults to 00:00 and this
messes up the calclation of the time worked. Can anyone point me in the
right direction with this. Many thanks.

Pete
 
It might be better to enter the dates and times--it'll make calculations much
safer.

or you could use a formula like this:

=A1-A2+(IF(A2>A1,1,0))
or
=a1-a2+(a2>a1)
 
Hi Dave

I tried this but it still produced a zero figure. However it also made me
aware of another problem as well. I need to count the amount of time
recorded and total the number of hours and minutes. This is also subject to
the same problems in that it will only record time up to a total of 23hours
and 59 minutes.

Here is an example of what I am trying to achieve with the spread sheet:
All cells are formatted hh:mm

Day1
Shift1 Shift2 Shift3
on off on off on off
b2 c2 e2 f2 h2 i2

Time S1 Time S2 Time S3
c2-b2 f2-e2 i2-h2

Total Time Day 1
Time S1+Time S2+Time S3

Problems are:
If 00:00 on and 24:00 off are recorded the total comes out as 00:00 instead
of 24:00
If I sum up the days 1 to 7 I can only record the number of hours up to
23:59 instead of a max of 168:00
To make matters worse I then need to record the weekly totals for thirteen
weeks with a theoretical maximum of 13 x 168 so I can then divide this to
provide an average weekly total.

I agree that to enter dates and times would be a more reliable way of doing
things but my problem is that I must maintain a degree of user friendliness
and also mirror if possible an existing paper based system to minimise
training implications. I have considered using a start date on the form as
a reference point for the date so the date is only entered once. Could I
use the information from more than one cell to make this calculation work
and if so how?

Many thanks for your interest and time spent in replying to me on this.

kind regards
Pete
 
You tried the alternate formulas?

For you sum problem, if you give the cell a custom format of [h]:mm, the hours
will continue to show after 24.
 
Hi Dave

This is exactly what I wanted to achieve. Many many thanks for your prompt
assistance with this.

kind regards

Pete

Dave Peterson said:
You tried the alternate formulas?

For you sum problem, if you give the cell a custom format of [h]:mm, the hours
will continue to show after 24.
Hi Dave

I tried this but it still produced a zero figure. However it also made me
aware of another problem as well. I need to count the amount of time
recorded and total the number of hours and minutes. This is also subject to
the same problems in that it will only record time up to a total of 23hours
and 59 minutes.

Here is an example of what I am trying to achieve with the spread sheet:
All cells are formatted hh:mm

Day1
Shift1 Shift2 Shift3
on off on off on off
b2 c2 e2 f2 h2 i2

Time S1 Time S2 Time S3
c2-b2 f2-e2 i2-h2

Total Time Day 1
Time S1+Time S2+Time S3

Problems are:
If 00:00 on and 24:00 off are recorded the total comes out as 00:00 instead
of 24:00
If I sum up the days 1 to 7 I can only record the number of hours up to
23:59 instead of a max of 168:00
To make matters worse I then need to record the weekly totals for thirteen
weeks with a theoretical maximum of 13 x 168 so I can then divide this to
provide an average weekly total.

I agree that to enter dates and times would be a more reliable way of doing
things but my problem is that I must maintain a degree of user friendliness
and also mirror if possible an existing paper based system to minimise
training implications. I have considered using a start date on the form as
a reference point for the date so the date is only entered once. Could I
use the information from more than one cell to make this calculation work
and if so how?

Many thanks for your interest and time spent in replying to me on this.

kind regards
Pete

calculations
much from
work. calculations
are and
this
Click to expand...
Click to expand...
 
Back
Top