Please help: I'm not very good with formulas.

=IF(('May 3'!M17)<15,"15:00:00","(May 3'M17)")

May 3 M17 represents hours to carry over from the previous sheet/period to
this period. This could sometimes by negative.

The rule is that you can only ever carry over 15 hours into the next month.
The formula above needs tweaking as it still shows 15:00:00 even if the value
in the previous sheet is less than 15:00:00.

Im using 1904 date format and [h]:mm:ss format.

=IF('May 3'!M17<15,"15:00:00",'May 3'!M17)

"Josh UK" wrote:

Please help: I'm not very good with formulas.

=IF(('May 3'!M17)<15,"15:00:00","(May 3'M17)")

May 3 M17 represents hours to carry over from the previous sheet/period to
this period. This could sometimes by negative.

The rule is that you can only ever carry over 15 hours into the next month.
The formula above needs tweaking as it still shows 15:00:00 even if the value
in the previous sheet is less than 15:00:00.

Im using 1904 date format and [h]:mm:ss format.

Try:

=MAX('May 3'!M17,Time(15,,))

--
HTH

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


with @tiscali.co.uk


"Josh UK" wrote in message
...
Please help: I'm not very good with formulas.

=IF(('May 3'!M17)<15,"15:00:00","(May 3'M17)")

May 3 M17 represents hours to carry over from the previous sheet/period to
this period. This could sometimes by negative.

The rule is that you can only ever carry over 15 hours into the next
month.
The formula above needs tweaking as it still shows 15:00:00 even if the
value
in the previous sheet is less than 15:00:00.

Im using 1904 date format and [h]:mm:ss format.

Shouldn't this be:

=MIN('May 3'!M17,Time(15,,))

?

The carry-forward time shouldn't exceed 15 hours, even if 'May 3'!M17
does.

Pete

On Feb 14, 7:13 pm, "Sandy Mann" wrote:
Try:

=MAX('May 3'!M17,Time(15,,))

--
HTH

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


with @tiscali.co.uk

"Josh UK" wrote in message

...



Please help: I'm not very good with formulas.

=IF(('May 3'!M17)<15,"15:00:00","(May 3'M17)")

May 3 M17 represents hours to carry over from the previous sheet/period to
this period. This could sometimes by negative.

The rule is that you can only ever carry over 15 hours into the next
month.
The formula above needs tweaking as it still shows 15:00:00 even if the
value
in the previous sheet is less than 15:00:00.

Im using 1904 date format and [h]:mm:ss format.- Hide quoted text -

- Show quoted text -

Yes you're quite correct now that I read what the OP wrote again.

--
HTH

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


with @tiscali.co.uk


"Pete_UK" wrote in message
ups.com...
Shouldn't this be:

=MIN('May 3'!M17,Time(15,,))

?

The carry-forward time shouldn't exceed 15 hours, even if 'May 3'!M17
does.

Pete

On Feb 14, 7:13 pm, "Sandy Mann" wrote:
Try:

=MAX('May 3'!M17,Time(15,,))

--
HTH

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


with @tiscali.co.uk

"Josh UK" wrote in message

...



Please help: I'm not very good with formulas.

=IF(('May 3'!M17)<15,"15:00:00","(May 3'M17)")

May 3 M17 represents hours to carry over from the previous sheet/period
to
this period. This could sometimes by negative.

The rule is that you can only ever carry over 15 hours into the next
month.
The formula above needs tweaking as it still shows 15:00:00 even if the
value
in the previous sheet is less than 15:00:00.

Im using 1904 date format and [h]:mm:ss format.- Hide quoted text -

- Show quoted text -

Hi Pete, thanks for your help.

This seems to work on all but one of the sheets. Even when I deleted the
sheet that was returning the #REF! Error and replaced it with sheet that did
work and relabelled it, each time I entered the formula I got the #REF!
Error and the Update Values box appeared, as if to search for a new file.

The formula that returns the error is: =MIN('Feb 1'!M17,TIME(15,,))

It seems sensitive to 'Feb 1'

"Pete_UK" wrote:

Shouldn't this be:

=MIN('May 3'!M17,Time(15,,))

?

The carry-forward time shouldn't exceed 15 hours, even if 'May 3'!M17
does.

Pete

On Feb 14, 7:13 pm, "Sandy Mann" wrote:
Try:

=MAX('May 3'!M17,Time(15,,))

--
HTH

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


with @tiscali.co.uk

"Josh UK" wrote in message

...



Please help: I'm not very good with formulas.

=IF(('May 3'!M17)<15,"15:00:00","(May 3'M17)")

May 3 M17 represents hours to carry over from the previous sheet/period to
this period. This could sometimes by negative.

The rule is that you can only ever carry over 15 hours into the next
month.
The formula above needs tweaking as it still shows 15:00:00 even if the
value
in the previous sheet is less than 15:00:00.

Im using 1904 date format and [h]:mm:ss format.- Hide quoted text -

- Show quoted text -