How to subtract..... [SOLVED]

[James Perry]

Hello guys,


How do i substract Date and Time in Excel ??

I have my data below:

A1 = 13/06/2013 15:47 PM

A2 = 14/06/2013 09:30 AM

I want to calculate difference in Day and Time Format in single cell.


ThanX.

 

[jeffreyweir]

Some good answers at http://stackoverflow.com/questions/7929058/format-a-time-span-as-a-number-of-days-hours-and-minutes. Given dates in A1 and A2 then this will do it:

=CONCATENATE(TEXT(FLOOR(A2-A1,1),"@")," Days",TEXT(A2-A1," h:mm:ss"))

 

[NARAYANK991]

Hi James ,


A couple of points :


1. 15:47 PM is not acceptable as a valid time ; it should be either just 15:47 , or 3:47 PM.


2. Can you give an example of the output you want , if we assume that the two dates are say 3 days apart ? Or will the two date + time combinations always be just one day apart ? If so , then just subtracting the two , and formatting the result cell as [hh]:mm:ss will give you the answer in hours.


Narayan

 

[oldchippy]

Hi,


A2-A1, then Custom Format


d "days" h "hrs" mm "mins"

 

[SirJB7]

@James Perry

Hi!

Just with a quick search here you might have found many topics regarding this subject, here's one link:

http://chandoo.org/forums/topic/finding-ages

Regards!

 

[James Perry]

Thank u guys. Here's how i did it.


1. Format cells A1 & A2 as: dd/mm/yyyy hh:mm:ss

2. cell A1 = 19/06/2013 11:00:00 pm

3. cell A2 = 20/06/2013 11:00:00 am

4. cell A3 = TEXT(A2-A1,"dd:hh:mm") = 00:12:00


Best wishes,

James

 

[oldchippy]

Hi James,


You could use =A2-A1 and custom format dd:hh:mm

 

[James Perry]

Many thanks, oldchippy.