Subtracting date and time

[Manish Kumawat]

Hi Ninja

I have a date and time in one cell i.e 4 July 2014 15:54:00 PM and a future date in next cell 8 august 2014 8:56:54 AM

Now i want to subtruct from 8 Aug to 4 July that gives me 35 days but what about time.

Please help me

 

[Hui]

=int(last date )-int(first date)

 

[Debraj]

Hi Manish,

I think, excel is giving you correct result.. Just you need to change the Number Format, so that you can view the actual output..

Try to convert result cell as "DD HH:MM:SS" or use below formula..

=TEXT(B1-A1,"DD hh:mm:ss")

 

[Hui]

You need to be careful as if the ending time is less than the start time you'll subtract an extra day, which I don't think is what you want.

 

[pecoflyer]

Hi Manish,

I think, excel is giving you correct result.. Just you need to change the Number Format, so that you can view the actual output..

Try to convert result cell as "DD HH:MM:SS" or use below formula..
=TEXT(B1-A1,"DD hh:mm:ss")

Does the answer need te be text ? =B1-A1 formatted as required will keep the reult a number

 

[Manish Kumawat]

Thanks for the reply Hui.. But it did not work for me.

kindly find the file and put your formula there.

Thanks
Manish

 

[NARAYANK991]

Hi Manish ,

Try this :

=TEXT(INT(B8-B7),"#") & " days , and " & TEXT((B8-B7)-INT(B8-B7),"HH:MM:SS")

Narayan

 

[Manish Kumawat]

Thanks Narayan.. It worked