Difference between two date & time (result in hours)

0

Hello All,

I'm trying to get differene between two date & time and unable to get the results in hours.

Please help me to resolve my problem.

Thank you!!

Start 09-24-2018 ‎11‎:‎49‎:‎53‎ ‎PM IST End 09-25-2018 ‎1‎:‎49‎:‎53‎ ‎PM IST Difference  ?????
Answer
Discuss

Answers

0
Selected Answer

Your data doesn't contain dates. Therefore the text you have must be converted to dates before it can be used in calculations. This task is made more difficult by 4 factors.

  1. Your data are probably generated from a database and contain invisible Unicode characters. This formula removes them.
    SUBSTITUTE($B3,UNICHAR(8206),"")
  2. The time format seems to be h:m:s instead of hh:mm:ss which about trebles the size of the formula because the size of the time string must be determined. This is done by finding its end, subtracting from that position its beginning and adding 2 characters (either AM or PM which, actually, is a fifth complicating factor). All the FIND functions in the formula deal with this problem. If you can influence the data format it would be a lot easier.
  3. You seem to work with mm-dd-yyyy dates (American style). To read dd-mm-yyyy dates my formula must be modified to swap day and month extractions:-
    VALUE(MID($B3,7,4)),VALUE(LEFT($B3,2))
  4. Your data contains a time zone which can't be interpreted by Excel as part of a time string. I am removing it.

So, the formula in B3 is below. Copy it down to B4.

=DATE(VALUE(MID($B3,7,4)),VALUE(LEFT($B3,2)),VALUE(MID($B3,4,2)))+TIMEVALUE(MID(SUBSTITUTE($B3,UNICHAR(8206),""),FIND(" ",SUBSTITUTE($B3,UNICHAR(8206),""))+1,FIND(" ",SUBSTITUTE($B3,UNICHAR(8206),""),FIND(" ",SUBSTITUTE($B3,UNICHAR(8206),""))+1)-FIND(" ",SUBSTITUTE($B3,UNICHAR(8206),""))+2))

Once you start dissecting it you will find that extracts the date string and converts it to a date, which is a number in Excel. To that number the TimeValue of the time string is added.

To calculate the difference, simply =B4-B3 and format the result as Time.

Discuss

Discussion

You are Virtuoso!!

I'll analyize your formula and get back to you if have any questions

Thank you Variatus.... :)
narendra_749yahoocom (rep: 14) Oct 17, '18 at 9:31 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login