Solved

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

Posted on 2016-09-22
13
75 Views
Last Modified: 2016-10-31
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?
0
Comment
Question by:cowboywm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +3
13 Comments
 
LVL 15

Expert Comment

by:gplana
ID: 41811419
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!
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41811436
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>.
0
 
LVL 1

Expert Comment

by:Vijay R
ID: 41811452
Hi
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:
=IF(NUMBERVALUE(MID(C2,1,2),0)<31,D2,"")
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
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41811509
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.
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 41812085
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")
0
 

Author Comment

by:cowboywm
ID: 41812746
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")
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41812777
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.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41815549
Likewise the time columns could be adjusted.

Adjustments to any of the dependent cells will affect the result.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41816226
Who removed my code from my first post?  And why???
0
 

Author Comment

by:cowboywm
ID: 41834508
This solution is the easiest to understand =IF(B2-A2<TIME(0,31,0),"Must Attend A Second Session","No Action Needed")
0
 

Author Comment

by:cowboywm
ID: 41866155
Solution works great.  Thank you.
0

Featured Post

Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question