Unable to get the proper time and date output with dd hh:mm:ss

[mark888]

Hi Guys,

Need the experts help on this. Been cracking my head for awhile now with no results

I have an excel file where I need to minus two dates and time as below:

A B C D E
1 Dec 01, 2014 15:51:40 Dec 16, 2014 16:53:32 15 1:1:52 8 20:46:43

The formula im using is =INT(B1-A1)&" "&HOUR(MOD(B1-A1,1))&":"&MINUTE(MOD(B1-A1,1))&":"&SECOND(MOD(B1-A1,1))
and the output i'm getting is in C1. How do I instead get the output of 15 01:01:52 instead of 15 1:1:52? I have tried using =B1-A1 but it does not show the correct value if it is more than 31 days hence the reason why I had to use the long formula.

The reason I need the output as 15 01:01:52 is because I have another column which I need to calculate the difference between column C1-D1 and for that I am using the formula =LEFT(C1,FIND(" ",C1)-1)+RIGHT(C1,8)-(LEFT(D1,FIND(" ",D1)-1)+RIGHT(D1,8)) and it will only accept the calculation if its 15 01:01:52. I could change the 8 to 6 but then I would need to change for a lot of records.

Is there anyone out there who can help me please

[samba_ravi]

Welcome to the forum
Please attach sample fileAttach A File.jpg

[azumi]

Maybe:
=INT(B1)-INT(A1)&" "&TEXT(MOD(B1,1)-MOD(A1,1),"hh:mm:ss")

[FDibbins]

If those really are dates/times, and not just text looking like dates/times, why would you want to convert them to text? It would seem to me to make more sense if you left dates/times in their numeric format, then you can run your calcs on them relatively easily?

[mark888]

Hi Azumi...Tried your formula but it only works halfway. It works for the above example but when I tried with another example it showed an error.

A B
1 Dec 15, 2014 20:39:13 Dec 17, 2014 17:05:50


When I tried your formula for above, B1-A1, it shows as error. Is there anything can be done?

[mark888]

Hi FDibbins,

Sorry I'm still a bit new to excel. The main reason is I need to minus 3 different cells with different data to get the result. The raw data is pulled from a tool. The reason why I subtract from B1-A1 is so that I can have that value in cell C1 and I need to use the value of C1 to subtract cell D1. Sorry if i'm cofusing anyone but in easier terms I would like to subtract B1-A1 then minus C1 and the result would be in D1.

the format in C1 cell is dd hh:mm:ss

A1 - Dec 01, 2014 15:51:40
B1 - Dec 16, 2014 16:53:32
C1 - 8 20:46:43

[mark888]

If those really are dates/times, and not just text looking like dates/times, why would you want to convert them to text? It would seem to me to make more sense if you left dates/times in their numeric format, then you can run your calcs on them relatively easily?

Hi FDibbins,

Sorry I'm still a bit new to excel. The main reason is I need to minus 3 different cells with different data to get the result. The raw data is pulled from a tool. The reason why I subtract from B1-A1 is so that I can have that value in cell C1 and I need to use the value of C1 to subtract cell D1. Sorry if i'm cofusing anyone but in easier terms I would like to subtract B1-A1 then minus C1 and the result would be in D1.

the format in C1 cell is dd hh:mm:ss

A1 - Dec 01, 2014 15:51:40
B1 - Dec 16, 2014 16:53:32
C1 - 8 20:46:43

[mark888]

Maybe:
=INT(B1)-INT(A1)&" "&TEXT(MOD(B1,1)-MOD(A1,1),"hh:mm:ss")

Hi Azumi...Tried your formula but it only works halfway. It works for the above example but when I tried with another example it showed an error.

A B
1 Dec 15, 2014 20:39:13 Dec 17, 2014 17:05:50


When I tried your formula for above, B1-A1, it shows as error. Is there anything can be done?

[samba_ravi]

=INT((B1-A1)-LEFT(C1,FIND(" ",C1)-1)+MID(C1,FIND(" ",C1)+1,8))&" "&TEXT(MOD((B1-A1)-LEFT(C1,FIND(" ",C1)-1)+MID(C1,FIND(" ",C1)+1,8),1),"HH:MM:SS")
Try this

[mark888]

=INT((B1-A1)-LEFT(C1,FIND(" ",C1)-1)+MID(C1,FIND(" ",C1)+1,8))&" "&TEXT(MOD((B1-A1)-LEFT(C1,FIND(" ",C1)-1)+MID(C1,FIND(" ",C1)+1,8),1),"HH:MM:SS")
Try this

Hi NFLSales,

That worked perfectly!!!!
Although if the column C1 is empty, is there anyway for it not to show an error as #VALUE? If possible would like it to accept as a cell value 00 00:00:00 that way formula still will work.

Is there any way to do this?

[samba_ravi]

=INT((B1-A1)-IFERROR(LEFT(C1,FIND(" ",C1)-1)+MID(C1,FIND(" ",C1)+1,8),0))&" "&TEXT(MOD((B1-A1)-IFERROR(LEFT(C1,FIND(" ",C1)-1)+MID(C1,FIND(" ",C1)+1,8),0),1),"HH:MM:SS")
Try this

[mark888]

=INT((B1-A1)-IFERROR(LEFT(C1,FIND(" ",C1)-1)+MID(C1,FIND(" ",C1)+1,8),0))&" "&TEXT(MOD((B1-A1)-IFERROR(LEFT(C1,FIND(" ",C1)-1)+MID(C1,FIND(" ",C1)+1,8),0),1),"HH:MM:SS")
Try this

Hi again NFLSales,

It works wonderfully for that. Although I did try it with another value as below:

A1 - Nov 08, 2014 07:21:14
B1 - Dec 16, 2014 22:14:29
C1 - 7 21:42:25
D1 - 30 21:16:09

When I ran the formula it returned the result in column D1 but I did a comparison and calculated it manually and also using an online calculator and the result shown is 30 17:10:50 instead of 30 21:16:09. Might be calculation is wrong somewhere?

[mark888]

Hi again NFLSales,

It works wonderfully for that. Although I did try it with another value as below:

A1 - Nov 08, 2014 07:21:14
B1 - Dec 16, 2014 22:14:29
C1 - 7 21:42:25
D1 - 30 21:16:09

When I ran the formula it returned the result in column D1 but I did a comparison and calculated it manually and also using an online calculator and the result shown is 30 17:10:50 instead of 30 21:16:09. Might be calculation is wrong somewhere?

Hi NFLSales,

Sorry my bad it's working fine..I made an error in validating. Thank you very very much for your help NFLSales and the rest for helping me out!!!
Works wonderfully now!!!

[samba_ravi]

See below and pls. recheck your calculation
B1 =41989 22:14:29
A1 =41951 7:21:14
B1-A1= 38 14:53:15
C1 = 7 21:42:25
D1 = 30 17:10:50