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("00:30:00"))

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

HTH,
Bernie
MS Excel MVP


"PCERM" wrote in message
...
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

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

"Bernie Deitrick" wrote:

Peter,

A formula along the lines of

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

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

HTH,
Bernie
MS Excel MVP


"PCERM" wrote in message
...
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,

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 MVP


"PCERM" wrote in message
...
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

"Bernie Deitrick" wrote:

Peter,

A formula along the lines of

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

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

HTH,
Bernie
MS Excel MVP


"PCERM" wrote in message
...
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

Many thanks again Bernie

I think I should be able to solve it now.

Thanks for your help.

Peter

"Bernie Deitrick" wrote:

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 MVP


"PCERM" wrote in message
...
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

"Bernie Deitrick" wrote:

Peter,

A formula along the lines of

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

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

HTH,
Bernie
MS Excel MVP


"PCERM" wrote in message
...
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