How do I construct an Excel formula based on elapsed minutes?

Using the data below, I want to build a formula that says, if the duration is less than 31 minutes, I want the message MUST ATTEND A SECOND SESSION displayed in Cell D
      A                     B               C                           D
Start Time    End Time   Duration               Action
10:10 AM      10:29 AM   19 min        MUST ATTEND A SECOND SESSION
9:15 AM        10:28 AM    73 min       No Action Needed
10:08 AM      10:12 AM    5 min         MUST ATTEND A SECOND SESSION

How do I construct the formula?
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

Rob HensonConnect With a Mentor Finance AnalystCommented:
Alternatively, ignore the existing Duration column which may or not contain text, use the time columns instead:

=IF(B2-A2<TIME(0,31,0),"Must Attend A Second Session","No Action Needed")
If you have the number of minutes on cell C then it's really easy.

You just have to use a conditional:

put on D2 this formula:
=if(c2<31,"MUST ATTEND A SECOND SESSION","No Action Needed")

Then you can expand this formula to D3, D4 and so on just by dragging the lower edge.

Also, please understand that column C need to have just the number of minutes, without the "min" word.

Hope it helps. Regards!
Scott PletcherSenior DBACommented:
You can allow for trailing char(s) with some extra coding.

Copy the formula below into D2.

Then copy from D2 to D3..D<whatever>.
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Vijay RCommented:
It can be done thru program (sub macro) or function in excel sheet(worksheet function). I guess you are referring to second option and giving the asnwer below:
Here MID() function gives part of a string (from column C), NUMBERVALUE() converts the text into number value. Just place this formula directly in any cell and copy it in three subsequent rows.
Here the assumption is column C (19 min  ) is string data type and has no spaces in the beginning as it takes first two characters.
Let me know if this is good for you or not.
Vijay R
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Assuming C2 is a text string like "19 min", then try this....

In D2
=IFERROR(IF(LEFT(C2,FIND(" ",C2)-1)+1<31,"Must Attend A Second Session","No Action Needed"),"")

Open in new window

and copy down.
cowboywmAuthor Commented:
Although, each recommendation has its own merits, this solution is by far the easier statement to construct.

=IF(B2-A2<TIME(0,31,0),"Must Attend A Second Session","No Action Needed")
Scott PletcherSenior DBACommented:
IF you assume that nothing else can affect the value in the "Duration" column.  But, since a separate, explicit column is computed, I would hate to assume that other adjustments couldn't be made to that value apart from a simple column difference.  It could be true, but I still hate to assume that it's not possible for the Duration column to be in some way adjusted from the value originally calculated.
Rob HensonFinance AnalystCommented:
Likewise the time columns could be adjusted.

Adjustments to any of the dependent cells will affect the result.
Scott PletcherSenior DBACommented:
Who removed my code from my first post?  And why???
cowboywmAuthor Commented:
This solution is the easiest to understand =IF(B2-A2<TIME(0,31,0),"Must Attend A Second Session","No Action Needed")
cowboywmAuthor Commented:
Solution works great.  Thank you.
All Courses

From novice to tech pro — start learning today.