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, 2022 in Others by Kithuzzz
• 27,940 points
104 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, 2022 by narikkadan
• 51,600 points

Related Questions In Others

+1 vote
1 answer
0 votes
1 answer

Average TIME Calculation differs between Excel and PowerBI

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

answered Sep 21, 2022 in Others by narikkadan
• 51,600 points
699 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, 2022 in Others by narikkadan
• 51,600 points
89 views
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, 2022 in Others by narikkadan
• 51,600 points
373 views
0 votes
0 answers

MS Excel - SumProduct formula with Loop

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

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

In excel how do I reference the current row but a specific column?

Put a $ symbol in front of ...READ MORE

answered Oct 15, 2022 in Others by narikkadan
• 51,600 points
200 views
0 votes
1 answer

Excel function for divide or split number to maximum possible equal parts

The underlying math for this is as ...READ MORE

answered Oct 17, 2022 in Others by narikkadan
• 51,600 points
228 views
0 votes
0 answers

How to create DropDown which have dynamic Validation List

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

Oct 31, 2022 in Others by Kithuzzz
• 27,940 points
135 views
0 votes
1 answer

Excel Formula which places date/time in cell when data is entered in another cell in the same row

Here's how to accomplish things in another ...READ MORE

answered Dec 11, 2022 in Others by narikkadan
• 51,600 points
65 views
0 votes
1 answer

Excel: Is it possible to reorder the data in 2 columns to match up if they have a certain number of characters / a string in common?

Try this: =LET(files,A1:A4, URLs,B1:B4, f,BYROW(files,LAMBDA(r,TEX ...READ MORE

answered Jan 21 in Others by narikkadan
• 51,600 points
41 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