if statement with time and "(" part of time calculation

time and if statement IF formulas

2 replies to this topic

#1 power pivot

power pivot

Newbie

• Members
• 4 posts

Posted 09 July 2015 - 01:15 PM

I am working with vessels and calculating the scheduled time vs. the arrival time.  Often it is more than 24 hours.  When I calculate scheduled to arrived, I do the following:

Example:

Scheduled:  04/05/15 03:00           Arrived:  04/08/15 17:52

Scheduled date and time in cell A1, Arrived date and time in cell H1.  Formula =(h1-A1)*24/24, then customized as [h]:mm format.  This works perfect.

However, sometimes the vessel arrives before it was scheduled and the calculation does not work.

I tried to do an if statement, but excel is not recognizing the "(" as part of the first argument. I want the if statement to say,

Argument =if(h1-a1)*24/24<0 if that is "true", then 0, if that is false than (h1-a1)*24/24.  How do I make excel understand the argument already includes a "("?

#2 power pivot

power pivot

Newbie

• Members
• 4 posts

Posted 09 July 2015 - 01:24 PM

Never mind....I figured it out.  I just needed to add an additional "(" in the front to separate it all.  Works fine now.

=if((H1-A1)*24/24<0,0(H1-A1)*24/24)

#3 Jonathan

Jonathan

• 887 posts

Posted 09 July 2015 - 06:23 PM

I'm glad you were able to solve the problem.

For anyone else with a similar problem, you can find a video lesson explaining the IF function in: Expert Skills Lesson 3-5 Use the IF logic function.

For a more general explanation of Excel formulas, see the video lesson: Essential Skills Lesson 2-12 Create your own formulas.

Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.

Also tagged with one or more of these keywords: time and if statement, IF, formulas

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users