How to limit time values outputted in certain cell

vintage36

Board Regular
Joined
Nov 29, 2011
Messages
58
A B C

1 07:09 08:30 01:21

2 06:43 08:30 01:47

3 05:29 08:30 03:01

Cols A and B above represent times entered in a spreadsheet with the interval between, e.g. A1 – B1 = C1.

However, any result in Col C must not exceed 02:00 (2 hours).

I need a formula I can apply to the whole of Col C to ensure the condition is obeyed throughout.

Please: are you able to advise?

Kindly ignore the the below attachment
 

Attachments

  • mrextab.gif
    mrextab.gif
    5.9 KB · Views: 5

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,066
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Something like this

Get Sheet Names.xlsx
ABC
17:098:3001:21
26:438:3001:47
35:298:3002:00
Sheet2
Cell Formulas
RangeFormula
C1:C3C1=MIN(120/1440,B1-A1)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,074
Messages
5,570,045
Members
412,308
Latest member
cornelb
Top