Determine if calculation between 2 date time values is 72 Hours in excel

0 votes

In order to establish a flag against each record when the time difference is smaller than 72 hours, I'm comparing booked DateTime values between two appointments.

It is straightforward to determine the difference in hours using the formula: Setting the cell format to [h]:mm after entering =B1-=A1

To determine whether the calculated amount is less than or larger than 72 hours, I then attempted to utilize an IF statement in a separate column. 

=IF(C1<72:00:00,1,0)

This, and variations of it using double quotes, etc hasn't worked for me. I guess the problem is that the calculated value in column C is still in DateTime format. Can anyone please advise the correct syntax to make the IF statement work?

Nov 12 in Others by Kithuzzz
• 20,660 points
43 views

1 answer to this question.

0 votes
Actually, Excel is quite accommodating in this regard. The important thing to remember is that date, time, and differences are expressed in decimals, with the full piece representing the number of days and the decimal representing the fractional days (hours, minutes).

So - for your example - after doing the subtraction, you should flag that particular field with =C1 < 3 ==> TRUE (if less than 72 hours), FALSE (otherwise)
answered Nov 12 by narikkadan
• 37,660 points

Related Questions In Others

+1 vote
1 answer
0 votes
1 answer

Calculate time difference in hours between two dates and times

Simply subtract the two dates to get ...READ MORE

answered Nov 4 in Others by narikkadan
• 37,660 points
101 views
0 votes
1 answer
+1 vote
1 answer

what is the difference between error and stderr in Node.js?

Error is an object created by Node.js to handle ...READ MORE

answered Jul 4, 2019 in Others by sunshine
• 1,280 points
1,300 views
0 votes
0 answers

MS Excel - SumProduct formula with Loop

1 I have 4 arrays of data where ...READ MORE

Feb 18 in Others by Edureka
• 13,640 points
82 views
0 votes
1 answer
0 votes
1 answer
0 votes
0 answers

How to create DropDown which have dynamic Validation List

In my data table, the columns "Category" ...READ MORE

Oct 31 in Others by Kithuzzz
• 20,660 points
51 views
0 votes
1 answer

Average TIME Calculation differs between Excel and PowerBI

They are calculating different things. In Power ...READ MORE

answered Sep 21 in Others by narikkadan
• 37,660 points
330 views
0 votes
1 answer

Excel Formula - if values in columns all contain X then return

You can use the AND function: = IF(AND(A3="OK";B3="OK";C3="OK");"everything ...READ MORE

answered Nov 4 in Others by narikkadan
• 37,660 points
38 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP