Hello,

I am using this formula (with thanks to Bob Phillips) to see if a block of
time falls with in a schedule:

'=MIN($W$1+TIME(0,30,0),$J4)-MAX($W$1,$I4)

W1 = 06:00
J4 = 14:30
I4 = 06:00

So I am really testing the half hour period of 6:00 and 06:30 to see if the
employee with the shift 06:00 to 14:30 is scheduled to work, which of course
he is.

I have two problems with this:

The cells that have the formula are formatted as time (13:30) and in my code
I have something that refers to "if the cell value is equal to "0:30" then
do something". In some cases, it is missing cells that it shouldn't.

The reason I formatted the cells to time in the first place is because some
cells were off fractionally, so I couldn't just say "if cell is = to 0.02083"

So - my first question is, what would be the best way to wrap my formula is
something (round, roundup??) that will force the result to the appropriate 30
minutes.

The second question is, how to I adapt the formula to account for the fact
that the shift end time in J4 may cross midnight. Also, the time in W1 may
cross midnight.

I understand the concepts, but I just can't get the formula right. Any help
would be very much appreciated!!

I can't find your original thread so I con't be sure what has gone on before
but try these in a COPY of your data:

To round to the nearest 30 minutes:

=ROUND(H1/(0.5/24),0)*(0.5/24)

For all times including times crossing midnight:

=MOD(MIN($W$1+TIME(0,30,0),$J4)-MAX($W$1,$I4),1)

--
HTH

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


with @tiscali.co.uk


"GettingThere" wrote in message
...
Hello,

I am using this formula (with thanks to Bob Phillips) to see if a block of
time falls with in a schedule:

'=MIN($W$1+TIME(0,30,0),$J4)-MAX($W$1,$I4)

W1 = 06:00
J4 = 14:30
I4 = 06:00

So I am really testing the half hour period of 6:00 and 06:30 to see if
the
employee with the shift 06:00 to 14:30 is scheduled to work, which of
course
he is.

I have two problems with this:

The cells that have the formula are formatted as time (13:30) and in my
code
I have something that refers to "if the cell value is equal to "0:30"
then
do something". In some cases, it is missing cells that it shouldn't.

The reason I formatted the cells to time in the first place is because
some
cells were off fractionally, so I couldn't just say "if cell is = to
0.02083"

So - my first question is, what would be the best way to wrap my formula
is
something (round, roundup??) that will force the result to the appropriate
30
minutes.

The second question is, how to I adapt the formula to account for the fact
that the shift end time in J4 may cross midnight. Also, the time in W1
may
cross midnight.

I understand the concepts, but I just can't get the formula right. Any
help
would be very much appreciated!!

If you want to check that the half hour period starting at W1 is entirely
within the shift I4 to J4 try this:


=IF(($I4<=$W$1)+($J4=$W$1)+($I4<=$W$1+"0:30")+($J 4=$W$1+"0:30")+($I4$J4)*2=4,"yes","no")

this caters for either time period crossing midnight and doesn't require any
rounding

"Sandy Mann" wrote:

I can't find your original thread so I con't be sure what has gone on before
but try these in a COPY of your data:

To round to the nearest 30 minutes:

=ROUND(H1/(0.5/24),0)*(0.5/24)

For all times including times crossing midnight:

=MOD(MIN($W$1+TIME(0,30,0),$J4)-MAX($W$1,$I4),1)

--
HTH

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


with @tiscali.co.uk


"GettingThere" wrote in message
...
Hello,

I am using this formula (with thanks to Bob Phillips) to see if a block of
time falls with in a schedule:

'=MIN($W$1+TIME(0,30,0),$J4)-MAX($W$1,$I4)

W1 = 06:00
J4 = 14:30
I4 = 06:00

So I am really testing the half hour period of 6:00 and 06:30 to see if
the
employee with the shift 06:00 to 14:30 is scheduled to work, which of
course
he is.

I have two problems with this:

The cells that have the formula are formatted as time (13:30) and in my
code
I have something that refers to "if the cell value is equal to "0:30"
then
do something". In some cases, it is missing cells that it shouldn't.

The reason I formatted the cells to time in the first place is because
some
cells were off fractionally, so I couldn't just say "if cell is = to
0.02083"

So - my first question is, what would be the best way to wrap my formula
is
something (round, roundup??) that will force the result to the appropriate
30
minutes.

The second question is, how to I adapt the formula to account for the fact
that the shift end time in J4 may cross midnight. Also, the time in W1
may
cross midnight.

I understand the concepts, but I just can't get the formula right. Any
help
would be very much appreciated!!

To allow for times outside the shift I4 to J4 amend to:

=MEDIAN($W$1+TIME(0,30,0),$I4,$J4)-MEDIAN($W$1,$I4,$IJ4)

Then apply rounding and mod functions as in Sandy's answer.

On 19 Mar, 00:42, daddylonglegs wrote:
If you want to check that the half hour period starting at W1 is entirely
within the shift I4 to J4 try this:

=IF(($I4<=$W$1)+($J4=$W$1)+($I4<=$W$1+"0:30")+($J 4=$W$1+"0:30")+($I4$J4)**2=4,"yes","no")

this caters for either time period crossing midnight and doesn't require any
rounding



"Sandy Mann" wrote:
I can't find your original thread so I con't be sure what has gone on before
but try these in a COPY of your data:

To round to the nearest 30 minutes:

=ROUND(H1/(0.5/24),0)*(0.5/24)

For all times including times crossing midnight:

=MOD(MIN($W$1+TIME(0,30,0),$J4)-MAX($W$1,$I4),1)

--
HTH

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


with @tiscali.co.uk

"GettingThere" wrote in message
...
Hello,

I am using this formula (with thanks to Bob Phillips) to see if a block of
time falls with in a schedule:

'=MIN($W$1+TIME(0,30,0),$J4)-MAX($W$1,$I4)

W1 = 06:00
J4 = 14:30
I4 = 06:00

So I am really testing the half hour period of 6:00 and 06:30 to see if
the
employee with the shift 06:00 to 14:30 is scheduled to work, which of
course
he is.

I have two problems with this:

The cells that have the formula are formatted as time (13:30) and in my
code
I have something that refers to "if the cell value is equal to "0:30"
then
do something". In some cases, it is missing cells that it shouldn't.

The reason I formatted the cells to time in the first place is because
some
cells were off fractionally, so I couldn't just say "if cell is = to
0.02083"

So - my first question is, what would be the best way to wrap my formula
is
something (round, roundup??) that will force the result to the appropriate
30
minutes.

The second question is, how to I adapt the formula to account for the fact
that the shift end time in J4 may cross midnight. Also, the time in W1
may
cross midnight.

I understand the concepts, but I just can't get the formula right. Any
help
would be very much appreciated!!- Hide quoted text -

- Show quoted text -

To allow for rounding and times crossing midnight, try:

=ROUND((MEDIAN(W1+"0:30",I4,J4+(J4<I4))-MEDIAN(W1,I4,J4+
(J4<I4)))*48,0)/48

On 19 Mar, 09:15, "Lori" wrote:
To allow for times outside the shift I4 to J4 amend to:

=MEDIAN($W$1+TIME(0,30,0),$I4,$J4)-MEDIAN($W$1,$I4,$IJ4)

Then apply rounding and mod functions as in Sandy's answer.

On 19 Mar, 00:42, daddylonglegs wrote:



If you want to check that the half hour period starting at W1 is entirely
within the shift I4 to J4 try this:

=IF(($I4<=$W$1)+($J4=$W$1)+($I4<=$W$1+"0:30")+($J 4=$W$1+"0:30")+($I4$J4)***2=4,"yes","no")

this caters for either time period crossing midnight and doesn't require any
rounding

"Sandy Mann" wrote:
I can't find your original thread so I con't be sure what has gone on before
but try these in a COPY of your data:

To round to the nearest 30 minutes:

=ROUND(H1/(0.5/24),0)*(0.5/24)

For all times including times crossing midnight:

=MOD(MIN($W$1+TIME(0,30,0),$J4)-MAX($W$1,$I4),1)

--
HTH

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


with @tiscali.co.uk

"GettingThere" wrote in message
...
Hello,

I am using this formula (with thanks to Bob Phillips) to see if a block of
time falls with in a schedule:

'=MIN($W$1+TIME(0,30,0),$J4)-MAX($W$1,$I4)

W1 = 06:00
J4 = 14:30
I4 = 06:00

So I am really testing the half hour period of 6:00 and 06:30 to see if
the
employee with the shift 06:00 to 14:30 is scheduled to work, which of
course
he is.

I have two problems with this:

The cells that have the formula are formatted as time (13:30) and in my
code
I have something that refers to "if the cell value is equal to "0:30"
then
do something". In some cases, it is missing cells that it shouldn't.

The reason I formatted the cells to time in the first place is because
some
cells were off fractionally, so I couldn't just say "if cell is = to
0.02083"

So - my first question is, what would be the best way to wrap my formula
is
something (round, roundup??) that will force the result to the appropriate
30
minutes.

The second question is, how to I adapt the formula to account for the fact
that the shift end time in J4 may cross midnight. Also, the time in W1
may
cross midnight.

I understand the concepts, but I just can't get the formula right. Any
help
would be very much appreciated!!- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

As I said I have not been able to follow the earlier discussions regarding
this problem, has the OP changed the subject?

I took it that W1 was a calculated return and the OP was saying that it may
be off be a fraction ( like "05:59") and so he/she wanted W1 rounded to the
nearest 30 minutes.

The formula should therefore have been:

=ROUND( <OP's formula /(0.5/24),0)*(0.5/24)

I also assumed that Bob's formula worked unless the times spanned midnight
and I thought that wrapping it in a MOD() function would correct this as it
normally does but I must confess I did not test it. While it does stop
Bob's formula returning "##########" for times crossing midnight ,it gives
unhelpful returns. Moral of the story don't mess with other peoples
formulas Sandy

My apologies to both the OP & Bob.

--
Regards,

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


with @tiscali.co.uk


"daddylonglegs" wrote in message
...
If you want to check that the half hour period starting at W1 is entirely
within the shift I4 to J4 try this:


=IF(($I4<=$W$1)+($J4=$W$1)+($I4<=$W$1+"0:30")+($J 4=$W$1+"0:30")+($I4$J4)*2=4,"yes","no")

this caters for either time period crossing midnight and doesn't require
any
rounding

"Sandy Mann" wrote:

I can't find your original thread so I con't be sure what has gone on
before
but try these in a COPY of your data:

To round to the nearest 30 minutes:

=ROUND(H1/(0.5/24),0)*(0.5/24)

For all times including times crossing midnight:

=MOD(MIN($W$1+TIME(0,30,0),$J4)-MAX($W$1,$I4),1)

--
HTH

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


with @tiscali.co.uk


"GettingThere" wrote in message
...
Hello,

I am using this formula (with thanks to Bob Phillips) to see if a block
of
time falls with in a schedule:

'=MIN($W$1+TIME(0,30,0),$J4)-MAX($W$1,$I4)

W1 = 06:00
J4 = 14:30
I4 = 06:00

So I am really testing the half hour period of 6:00 and 06:30 to see
if
the
employee with the shift 06:00 to 14:30 is scheduled to work, which of
course
he is.

I have two problems with this:

The cells that have the formula are formatted as time (13:30) and in my
code
I have something that refers to "if the cell value is equal to "0:30"
then
do something". In some cases, it is missing cells that it shouldn't.

The reason I formatted the cells to time in the first place is because
some
cells were off fractionally, so I couldn't just say "if cell is = to
0.02083"

So - my first question is, what would be the best way to wrap my
formula
is
something (round, roundup??) that will force the result to the
appropriate
30
minutes.

The second question is, how to I adapt the formula to account for the
fact
that the shift end time in J4 may cross midnight. Also, the time in W1
may
cross midnight.

I understand the concepts, but I just can't get the formula right. Any
help
would be very much appreciated!!