Days:Hours:Mins Calculation

[kingz]

Hi everyone,

i'm tryng to do a calculation between 2 cells

I want it to be days:hours:mins in both & calculate the difference between the 2 (must work either way .. positive or negative)

eg.

32 days 15 hours 5mins

22 days 10 hours 2 mins

= 10 days 5 hours 3 mins
​

i'm struggling to format & process it correctly! any help will be very much appreciated - many thanks

 

[Richard Schollar]

Hi

perhaps this:

Excel Workbook
	A
1	01/01/2010 08:23
2	09/08/2010 12:53
3	220 days 04 hours 29 minutes
Sheet2

Excel 2002

Cell Formulas
Range	Formula
A3	=INT(ABS(A1-A2)) & " days " & TEXT(ABS(A1-A2),"hh \h\o\u\r\s mm") & " minutes"

 

[kingz]

Hi Richard,

thanks for your reply

I don't want to calculate between dates - as I don't have that data!
I only have the amount of days, the hours & mins

 

[Matty]

Hi Richard,

Never seen the "hh \h\o\u\r\s mm" format used before. Interesting stuff.

However, it's strange that the formula evaluates to...

220 days 04 hours 29 minutes

...in your example, but on my PC it evaluates to:

220 days 04 hours 30 minutes

Matty

 

[Richard Schollar]

Hi Richard,

thanks for your reply

I don't want to calculate between dates - as I don't have that data!
I only have the amount of days, the hours & mins

What do you have in the cells? Is it literally text like "10 days 5 hours 30 minutes"? How come you have unfriendly non-numerics like this? (it would be so much easier if you had actual numbers)

 

[kingz]

What do you have in the cells? Is it literally text like "10 days 5 hours 30 minutes"? How come you have unfriendly non-numerics like this? (it would be so much easier if you had actual numbers)

I don't have them in single cells yet I have the data in individual cells but I would like to figure out a way to process them all together & work out the difference

edit: & I am only using numbers .. the text is not necessary

 

[Richard Schollar]

Perhaps like this:

Excel Workbook
	A	B	C
1	Days	Hours	Minutes
2	22	15	45
3	10	18	23
4
5	12.50833
6	12 days 12 hours 12 minutes
7
Sheet1

Excel 2002

Cell Formulas
Range	Formula
A5	=ABS(SUM(A2:C2/{1,24,1440})-SUM(A3:C3/{1,24,1440}))
A6	=INT(A5) & " days " & TEXT(A5,"hh ""hours"" mm") & " minutes"

 

[kingz]

Perhaps like this:

Excel Workbook
	A	B	C
1	Days	Hours	Minutes
2	22	15	45
3	10	18	23
4
5	12.50833
6	12 days 12 hours 12 minutes
7
Sheet1

Excel 2002

Cell Formulas
Range	Formula
A5	=ABS(SUM(A2:C2/{1,24,1440})-SUM(A3:C3/{1,24,1440}))
A6	=INT(A5) & " days " & TEXT(A5,"hh ""hours"" mm") & " minutes"

close .. but this doesn't appear to be processing the hours & mins correctly

 

[Richard Schollar]

Sorry, that needed to be array-entered. Here's an alternative (which doesn't require CSE):

Excel Workbook
	A	B	C
1	Days	Hours	Minutes
2	22	15	45
3	10	18	23
4
5	11.89027778
6	11 days 21 hours 22 minutes
Sheet8

Excel 2002

Cell Formulas
Range	Formula
A5	=ABS(SUMPRODUCT((A2:C2-A3:C3)/{1,24,1440}))
A6	=INT(A5) & " days " & TEXT(A5,"hh ""hours"" mm") & " minutes"

 

[kingz]

Sorry, that needed to be array-entered. Here's an alternative (which doesn't require CSE):

Excel Workbook
	A	B	C
1	Days	Hours	Minutes
2	22	15	45
3	10	18	23
4
5	11.89027778
6	11 days 21 hours 22 minutes
Sheet8

Excel 2002

Cell Formulas
Range	Formula
A5	=ABS(SUMPRODUCT((A2:C2-A3:C3)/{1,24,1440}))
A6	=INT(A5) & " days " & TEXT(A5,"hh ""hours"" mm") & " minutes"

excellent .. works a treat

thank you very much Richard - i'm off to test & reformat slightly