software

I have set up a time sheet and want the minimum time taken at lunch to show
no less than 30 minutes. At present there are start and finish times for
both am and pm. The lunchtime column is a simple calculation taking the
second start time from the first stop time thus producing a time figure ie
00:45, 00:20 etc. I want to change this to show that a minimum of 30 mins
has been taken irrespective of what has actually been taken. Can anyone help
please?

Thanks

Peter

Peter,

A formula along the lines of

=MAX(A2-A1,TIMEVALUE(quot;00:30:00quot;))

where A2 is the end of lunch, and A1 is the start of lunch.

HTH,
Bernie
MS Excel MVPquot;PCERMquot; gt; wrote in message
...
gt;I have set up a time sheet and want the minimum time taken at lunch to show
gt; no less than 30 minutes. At present there are start and finish times for
gt; both am and pm. The lunchtime column is a simple calculation taking the
gt; second start time from the first stop time thus producing a time figure ie
gt; 00:45, 00:20 etc. I want to change this to show that a minimum of 30 mins
gt; has been taken irrespective of what has actually been taken. Can anyone help
gt; please?
gt;
gt; Thanks
gt;
gt; Peter
Thanks Bernie

That worked well. My problem now is that the total times for working for
the day are calculated with the totals of other columns. In other words the
minimum anyone can take for lunch is 30 mins but hours actually worked are
not taking this into account. I have tried to include the column as a
subtraction of the totals but all I seem to get is rubbish coming out. Sorry
I am not very good at this Excel.

Peter

quot;Bernie Deitrickquot; wrote:

gt; Peter,
gt;
gt; A formula along the lines of
gt;
gt; =MAX(A2-A1,TIMEVALUE(quot;00:30:00quot;))
gt;
gt; where A2 is the end of lunch, and A1 is the start of lunch.
gt;
gt; HTH,
gt; Bernie
gt; MS Excel MVP
gt;
gt;
gt; quot;PCERMquot; gt; wrote in message
gt; ...
gt; gt;I have set up a time sheet and want the minimum time taken at lunch to show
gt; gt; no less than 30 minutes. At present there are start and finish times for
gt; gt; both am and pm. The lunchtime column is a simple calculation taking the
gt; gt; second start time from the first stop time thus producing a time figure ie
gt; gt; 00:45, 00:20 etc. I want to change this to show that a minimum of 30 mins
gt; gt; has been taken irrespective of what has actually been taken. Can anyone help
gt; gt; please?
gt; gt;
gt; gt; Thanks
gt; gt;
gt; gt; Peter
gt;
gt;
gt;

Peter,

The general form of the formula to calculate hours worked would be

=Cell with finishing time - Cell with starting time - Cell with the formula that I posted

formatted as time.

To figure out hours, the formula above *24.

Beyond that, I would need an example of what you have, cell addresses, formulas, etc...

HTH,
Bernie
MS Excel MVPquot;PCERMquot; gt; wrote in message
...
gt; Thanks Bernie
gt;
gt; That worked well. My problem now is that the total times for working for
gt; the day are calculated with the totals of other columns. In other words the
gt; minimum anyone can take for lunch is 30 mins but hours actually worked are
gt; not taking this into account. I have tried to include the column as a
gt; subtraction of the totals but all I seem to get is rubbish coming out. Sorry
gt; I am not very good at this Excel.
gt;
gt; Peter
gt;
gt; quot;Bernie Deitrickquot; wrote:
gt;
gt;gt; Peter,
gt;gt;
gt;gt; A formula along the lines of
gt;gt;
gt;gt; =MAX(A2-A1,TIMEVALUE(quot;00:30:00quot;))
gt;gt;
gt;gt; where A2 is the end of lunch, and A1 is the start of lunch.
gt;gt;
gt;gt; HTH,
gt;gt; Bernie
gt;gt; MS Excel MVP
gt;gt;
gt;gt;
gt;gt; quot;PCERMquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I have set up a time sheet and want the minimum time taken at lunch to show
gt;gt; gt; no less than 30 minutes. At present there are start and finish times for
gt;gt; gt; both am and pm. The lunchtime column is a simple calculation taking the
gt;gt; gt; second start time from the first stop time thus producing a time figure ie
gt;gt; gt; 00:45, 00:20 etc. I want to change this to show that a minimum of 30 mins
gt;gt; gt; has been taken irrespective of what has actually been taken. Can anyone help
gt;gt; gt; please?
gt;gt; gt;
gt;gt; gt; Thanks
gt;gt; gt;
gt;gt; gt; Peter
gt;gt;
gt;gt;
gt;gt;
Many thanks again Bernie

I think I should be able to solve it now.

Thanks for your help.

Peter

quot;Bernie Deitrickquot; wrote:

gt; Peter,
gt;
gt; The general form of the formula to calculate hours worked would be
gt;
gt; =Cell with finishing time - Cell with starting time - Cell with the formula that I posted
gt;
gt; formatted as time.
gt;
gt; To figure out hours, the formula above *24.
gt;
gt; Beyond that, I would need an example of what you have, cell addresses, formulas, etc...
gt;
gt; HTH,
gt; Bernie
gt; MS Excel MVP
gt;
gt;
gt; quot;PCERMquot; gt; wrote in message
gt; ...
gt; gt; Thanks Bernie
gt; gt;
gt; gt; That worked well. My problem now is that the total times for working for
gt; gt; the day are calculated with the totals of other columns. In other words the
gt; gt; minimum anyone can take for lunch is 30 mins but hours actually worked are
gt; gt; not taking this into account. I have tried to include the column as a
gt; gt; subtraction of the totals but all I seem to get is rubbish coming out. Sorry
gt; gt; I am not very good at this Excel.
gt; gt;
gt; gt; Peter
gt; gt;
gt; gt; quot;Bernie Deitrickquot; wrote:
gt; gt;
gt; gt;gt; Peter,
gt; gt;gt;
gt; gt;gt; A formula along the lines of
gt; gt;gt;
gt; gt;gt; =MAX(A2-A1,TIMEVALUE(quot;00:30:00quot;))
gt; gt;gt;
gt; gt;gt; where A2 is the end of lunch, and A1 is the start of lunch.
gt; gt;gt;
gt; gt;gt; HTH,
gt; gt;gt; Bernie
gt; gt;gt; MS Excel MVP
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; quot;PCERMquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt;I have set up a time sheet and want the minimum time taken at lunch to show
gt; gt;gt; gt; no less than 30 minutes. At present there are start and finish times for
gt; gt;gt; gt; both am and pm. The lunchtime column is a simple calculation taking the
gt; gt;gt; gt; second start time from the first stop time thus producing a time figure ie
gt; gt;gt; gt; 00:45, 00:20 etc. I want to change this to show that a minimum of 30 mins
gt; gt;gt; gt; has been taken irrespective of what has actually been taken. Can anyone help
gt; gt;gt; gt; please?
gt; gt;gt; gt;
gt; gt;gt; gt; Thanks
gt; gt;gt; gt;
gt; gt;gt; gt; Peter
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;