Time Calc - 24hrs vs 0hrs

Got a question... I'm using this formula to calculate time (overtime)

=(D4-B4+(D4<B4))*24

B4 is my starting time, D4 is my ending time. For the most part, this works pretty
good unless I work a full 24hr shift. If my shift starts at 07:00, ends 24hrs later at 07:00, this formula gives me a value of 0. How can I make this show 24hrs?

I'm using a form in excel, there's no room to format the cell using date & time,
(there's another cell that lists the date) it needs to read the time only. Once these forms are filled out, they are printed and submitted with our time sheets.

Any suggestions???


JF

On Tue, 18 Oct 2005 13:36:05 -0500, Ltat42a
<Ltat42a.1x43ed_1129662313.0713@excelforum-nospam.com> wrote:

>
>Got a question... I'm using this formula to calculate time (overtime)
>
>=(D4-B4+(D4<B4))*24
>
>B4 is my starting time, D4 is my ending time. For the most part, this
>works pretty
>good unless I work a full 24hr shift. If my shift starts at 07:00, ends
>24hrs later at 07:00, this formula gives me a value of 0. How can I make
>this show 24hrs?
>
>I'm using a form in excel, there's no room to format the cell using
>date & time,
>(there's another cell that lists the date) it needs to read the time
>only. Once these forms are filled out, they are printed and submitted
>with our time sheets.
>
>Any suggestions???
>
>
>JF

If you are working a full 24 hour shift, Excel will need to know the date
starting and ending.

If you did not have that information, how would Excel know, for example, that
if you started at 7 AM and stopped at 8 AM whether you had worked one hour or
25 hours?

Where Excel will get the date information from, if you are not entering it into
the same cell, may depend on the layout of your worksheet.


--ron

The form I'm using does have a cell for the date that the overtime is worked. The formula I'm using does not reference that cell.

If the date cell was included in the formula, is there a way the time calculation can be changed to reflect 24 hours instead of 0?

Thanx

JF

Thought I would give you my way.

I simply enter the time as 24.30 or 25.30 etc (or whatever time you finished
etc)
The cell can be formatted to read 12.30 am, 1.30am etc and the time
difference is calculated correctly.
If you finish mid afternoon etc simply add 24 to the normal figure you would
normally input.
e.g. 15:00 start on Monday to 15.00 finish on Tuesday.
type 15:00 to start. Type 39:00 to finish
--
Big Rick


"Ron Rosenfeld" wrote:

> On Tue, 18 Oct 2005 13:36:05 -0500, Ltat42a
> <Ltat42a.1x43ed_1129662313.0713@excelforum-nospam.com> wrote:
>
> >
> >Got a question... I'm using this formula to calculate time (overtime)
> >
> >=(D4-B4+(D4<B4))*24
> >
> >B4 is my starting time, D4 is my ending time. For the most part, this
> >works pretty
> >good unless I work a full 24hr shift. If my shift starts at 07:00, ends
> >24hrs later at 07:00, this formula gives me a value of 0. How can I make
> >this show 24hrs?
> >
> >I'm using a form in excel, there's no room to format the cell using
> >date & time,
> >(there's another cell that lists the date) it needs to read the time
> >only. Once these forms are filled out, they are printed and submitted
> >with our time sheets.
> >
> >Any suggestions???
> >
> >
> >JF
>
> If you are working a full 24 hour shift, Excel will need to know the date
> starting and ending.
>
> If you did not have that information, how would Excel know, for example, that
> if you started at 7 AM and stopped at 8 AM whether you had worked one hour or
> 25 hours?
>
> Where Excel will get the date information from, if you are not entering it into
> the same cell, may depend on the layout of your worksheet.
>
>
> --ron
>

On Tue, 18 Oct 2005 18:07:37 -0500, Ltat42a
<Ltat42a.1x4haa_1129680307.1899@excelforum-nospam.com> wrote:

>
>The form I'm using does have a cell for the date that the overtime is
>worked. The formula I'm using does not reference that cell.
>
>If the date cell was included in the formula, is there a way the time
>calculation can be changed to reflect 24 hours instead of 0?
>
>Thanx
>
>JF

I'm not sure I understand the form.

But if you are entering a start time and a stop time, and the difference could
be more than 24 hours, you need to somehow also reference a start date and stop
date. If this could be obtained from some other cells, then the formula you
would use would be:

=24 * ((StopDate+StopTime) - (StartDate+StartTime))

Format the result as number with an appropriate number of decimal places.

The logic:

Excel stores dates as serial numbers (starting with 1/1/1900 or 1/1/1904) and
times as fractions of a day. So the above formulas merely combine to produce
what you would obtain if you entered the date and time in the same cell.

Multiplying by 24 transforms the result into decimal hours, which can then be
multiplied by an hourly rate.


--ron

Originally Posted by Ron Rosenfeld

On Tue, 18 Oct 2005 18:07:37 -0500, Ltat42a
<Ltat42a.1x4haa_1129680307.1899@excelforum-nospam.com> wrote:

>
>The form I'm using does have a cell for the date that the overtime is
>worked. The formula I'm using does not reference that cell.
>
>If the date cell was included in the formula, is there a way the time
>calculation can be changed to reflect 24 hours instead of 0?
>
>Thanx
>
>JF

I'm not sure I understand the form.

But if you are entering a start time and a stop time, and the difference could
be more than 24 hours, you need to somehow also reference a start date and stop
date. If this could be obtained from some other cells, then the formula you
would use would be:

=24 * ((StopDate+StopTime) - (StartDate+StartTime))

Format the result as number with an appropriate number of decimal places.

The logic:

Excel stores dates as serial numbers (starting with 1/1/1900 or 1/1/1904) and
times as fractions of a day. So the above formulas merely combine to produce
what you would obtain if you entered the date and time in the same cell.

Multiplying by 24 transforms the result into decimal hours, which can then be
multiplied by an hourly rate.


--ron

As for the form, I have a "Time in" cell - the time you arrived for your shift,
I have a "Time out" cell - the time you ended your shift. In some cases, this will be a 24 hour shift, in other cases it will be less (we don't get paid for travel time from one station to the other). I also have a date cell.

Can my formula use a IF statement? If D4 = 07:00, & B4 = 07:00, the result would be 24, if anything else exists, then do the above calculation. I.E. - If B4 = 07:30 & D4 = 07:00 - the result is 23.5hrs.

My formula works good unless you work a 24hr shift - the result comes up 0.


Thanx.....JF

On Wed, 19 Oct 2005 07:36:03 -0500, Ltat42a
<Ltat42a.1x5hec_1129727108.6021@excelforum-nospam.com> wrote:

>
>Ron Rosenfeld Wrote:
>> On Tue, 18 Oct 2005 18:07:37 -0500, Ltat42a
>> <Ltat42a.1x4haa_1129680307.1899@excelforum-nospam.com> wrote:
>>
>> >
>> >The form I'm using does have a cell for the date that the overtime is
>> >worked. The formula I'm using does not reference that cell.
>> >
>> >If the date cell was included in the formula, is there a way the time
>> >calculation can be changed to reflect 24 hours instead of 0?
>> >
>> >Thanx
>> >
>> >JF
>>
>> I'm not sure I understand the form.
>>
>> But if you are entering a start time and a stop time, and the
>> difference could
>> be more than 24 hours, you need to somehow also reference a start date
>> and stop
>> date. If this could be obtained from some other cells, then the
>> formula you
>> would use would be:
>>
>> =24 * ((StopDate+StopTime) - (StartDate+StartTime))
>>
>> Format the result as number with an appropriate number of decimal
>> places.
>>
>> The logic:
>>
>> Excel stores dates as serial numbers (starting with 1/1/1900 or
>> 1/1/1904) and
>> times as fractions of a day. So the above formulas merely combine to
>> produce
>> what you would obtain if you entered the date and time in the same
>> cell.
>>
>> Multiplying by 24 transforms the result into decimal hours, which can
>> then be
>> multiplied by an hourly rate.
>>
>>
>> --ron
>
>As for the form, I have a "Time in" cell - the time you arrived for
>your shift,
>I have a "Time out" cell - the time you ended your shift. In some
>cases, this will be a 24 hour shift, in other cases it will be less (we
>don't get paid for travel time from one station to the other). I also
>have a date cell.
>
>Can my formula use a IF statement? If D4 = 07:00, & B4 = 07:00, the
>result would be 24, if anything else exists, then do the above
>calculation. I.E. - If B4 = 07:30 & D4 = 07:00 - the result is
>23.5hrs.
>
>My formula works good unless you work a 24hr shift - the result comes
>up 0.
>
>
>Thanx.....JF

If your maximum shift will never be more than 24 hours, then you could modify
your existing formula slightly:

Original:

=(D4-B4+(D4<B4))*24

Modified:

=(ISNUMBER(B4))*(ISNUMBER(D4))*(D4-B4+(D4<=B4))*24

The only time there will be a problem with ambiguity is if your work shift is
GREATER than 24 hours.

The ISNUMBER functions are so that the formula will return a zero (instead of a
24) if there are no entries in B4 and D4.



--ron

Originally Posted by Ron Rosenfeld

On Wed, 19 Oct 2005 07:36:03 -0500, Ltat42a
<Ltat42a.1x5hec_1129727108.6021@excelforum-nospam.com> wrote:

>
>Ron Rosenfeld Wrote:
>> On Tue, 18 Oct 2005 18:07:37 -0500, Ltat42a
>> <Ltat42a.1x4haa_1129680307.1899@excelforum-nospam.com> wrote:
>>
>> >
>> >The form I'm using does have a cell for the date that the overtime is
>> >worked. The formula I'm using does not reference that cell.
>> >
>> >If the date cell was included in the formula, is there a way the time
>> >calculation can be changed to reflect 24 hours instead of 0?
>> >
>> >Thanx
>> >
>> >JF
>>
>> I'm not sure I understand the form.
>>
>> But if you are entering a start time and a stop time, and the
>> difference could
>> be more than 24 hours, you need to somehow also reference a start date
>> and stop
>> date. If this could be obtained from some other cells, then the
>> formula you
>> would use would be:
>>
>> =24 * ((StopDate+StopTime) - (StartDate+StartTime))
>>
>> Format the result as number with an appropriate number of decimal
>> places.
>>
>> The logic:
>>
>> Excel stores dates as serial numbers (starting with 1/1/1900 or
>> 1/1/1904) and
>> times as fractions of a day. So the above formulas merely combine to
>> produce
>> what you would obtain if you entered the date and time in the same
>> cell.
>>
>> Multiplying by 24 transforms the result into decimal hours, which can
>> then be
>> multiplied by an hourly rate.
>>
>>
>> --ron
>
>As for the form, I have a "Time in" cell - the time you arrived for
>your shift,
>I have a "Time out" cell - the time you ended your shift. In some
>cases, this will be a 24 hour shift, in other cases it will be less (we
>don't get paid for travel time from one station to the other). I also
>have a date cell.
>
>Can my formula use a IF statement? If D4 = 07:00, & B4 = 07:00, the
>result would be 24, if anything else exists, then do the above
>calculation. I.E. - If B4 = 07:30 & D4 = 07:00 - the result is
>23.5hrs.
>
>My formula works good unless you work a 24hr shift - the result comes
>up 0.
>
>
>Thanx.....JF

If your maximum shift will never be more than 24 hours, then you could modify
your existing formula slightly:

Original:

=(D4-B4+(D4<B4))*24

Modified:

=(ISNUMBER(B4))*(ISNUMBER(D4))*(D4-B4+(D4<=B4))*24

The only time there will be a problem with ambiguity is if your work shift is
GREATER than 24 hours.

The ISNUMBER functions are so that the formula will return a zero (instead of a
24) if there are no entries in B4 and D4.



--ron

Thanx Ron.....no, there will never be a time when the shift is greater than 24hrs, if a person does work more than 24hrs, a new form will have to be filled out then submitted. I'll try that and see what happens.

Thanx! - JF

Originally Posted by Ron Rosenfeld

On Wed, 19 Oct 2005 07:36:03 -0500, Ltat42a
<Ltat42a.1x5hec_1129727108.6021@excelforum-nospam.com> wrote:

>
>Ron Rosenfeld Wrote:
>> On Tue, 18 Oct 2005 18:07:37 -0500, Ltat42a
>> <Ltat42a.1x4haa_1129680307.1899@excelforum-nospam.com> wrote:
>>
>> >
>> >The form I'm using does have a cell for the date that the overtime is
>> >worked. The formula I'm using does not reference that cell.
>> >
>> >If the date cell was included in the formula, is there a way the time
>> >calculation can be changed to reflect 24 hours instead of 0?
>> >
>> >Thanx
>> >
>> >JF
>>
>> I'm not sure I understand the form.
>>
>> But if you are entering a start time and a stop time, and the
>> difference could
>> be more than 24 hours, you need to somehow also reference a start date
>> and stop
>> date. If this could be obtained from some other cells, then the
>> formula you
>> would use would be:
>>
>> =24 * ((StopDate+StopTime) - (StartDate+StartTime))
>>
>> Format the result as number with an appropriate number of decimal
>> places.
>>
>> The logic:
>>
>> Excel stores dates as serial numbers (starting with 1/1/1900 or
>> 1/1/1904) and
>> times as fractions of a day. So the above formulas merely combine to
>> produce
>> what you would obtain if you entered the date and time in the same
>> cell.
>>
>> Multiplying by 24 transforms the result into decimal hours, which can
>> then be
>> multiplied by an hourly rate.
>>
>>
>> --ron
>
>As for the form, I have a "Time in" cell - the time you arrived for
>your shift,
>I have a "Time out" cell - the time you ended your shift. In some
>cases, this will be a 24 hour shift, in other cases it will be less (we
>don't get paid for travel time from one station to the other). I also
>have a date cell.
>
>Can my formula use a IF statement? If D4 = 07:00, & B4 = 07:00, the
>result would be 24, if anything else exists, then do the above
>calculation. I.E. - If B4 = 07:30 & D4 = 07:00 - the result is
>23.5hrs.
>
>My formula works good unless you work a 24hr shift - the result comes
>up 0.
>
>
>Thanx.....JF

If your maximum shift will never be more than 24 hours, then you could modify
your existing formula slightly:

Original:

=(D4-B4+(D4<B4))*24

Modified:

=(ISNUMBER(B4))*(ISNUMBER(D4))*(D4-B4+(D4<=B4))*24

The only time there will be a problem with ambiguity is if your work shift is
GREATER than 24 hours.

The ISNUMBER functions are so that the formula will return a zero (instead of a
24) if there are no entries in B4 and D4.



--ron

Hi Ron.....your answer works great!
If I insert 7:00 in both cells, the result returns 24 (just what I needed),
if I insert anything less (8:30 & 07:00) - the calculation returns the correct
number of hours - 22.5hrs.

Thank you very much for the help.....JF

On Wed, 19 Oct 2005 10:55:57 -0500, Ltat42a
<Ltat42a.1x5pqh_1129737909.3488@excelforum-nospam.com> wrote:

>Hi Ron.....your answer works great!
>If I insert 7:00 in both cells, the result returns 24 (just what I
>needed),
>if I insert anything less (8:30 & 07:00) - the calculation returns the
>correct
>number of hours - 22.5hrs.
>
>Thank you very much for the help.....JF
>
>
>--
>Ltat42a

You're most welcome. Thanks for the feedback.


--ron