Help with a formula

[Guest]

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.

 

[Guest]

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

 

[Sandy Mann]

Try:

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

--
HTH

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Pete_UK]

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

Try:

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

--
HTH

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

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 -

 

[Sandy Mann]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

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

Try:

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

--
HTH

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

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 -

 

[Guest]

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'

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

Try:

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

--
HTH

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

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 -