Hoe to deduct date and time in one cell

[marcidee]

Please can you tell me if there is a formula / code that will deduct date and time if date and time are in the same cell.

ie

31/07/2019 15:00:00 less 01/08/2019 16:00:00 - using format (dd/mm/yyyyy hh:mm)

Thank you Marc

 

[alansidman]

1. Use Power Query to split the column using the delimeter "less"
2. Close and Load to Excel
3. Add a column to the table in Excel
4. In new column "Difference" subtract Column B from A
5. Add new column to table "Days"
6. In column Days =Int(column Difference)
7. Add new column "Hours"
8. In new column Hours =Mod(column Difference,1)
9. Format Column Hours Custom 13:30


Data Range

	A​	B​	C​	D​	E​
1​	Column1.1​	Column1.2​	Difference​	Days​	Hours​
2​	07/31/2019 15:00:00​	08/01/2019 16:00:00​	1.041666667​	1​	1:00​

<tbody>
</tbody>

Note: I changed the date format for my calculation as I live in the US. You can do this using European Date Convention.

 

[marcidee]

Thank you for this - can you explain what I should be entering for point 6 above if answer of point 4 is in cell H9

 

[marcidee]

I have worked out row 6 - I am typing this for point 9 without success
=MOD(H9 Difference,1)

 

[Rick Rothstein]

Just out of curiosity, assuming your two dates are in cell A1, does this formula give you what you want directly...

=TEXT(TRIM(MID(A1,SEARCH("less",A1)+4,99))-TRIM(LEFT(A1,SEARCH("less",A1)-1)),"d ""day(s) ""h"" hour(s) ""m"" minute(s)""")

 

[marcidee]

thank you for this - the dates would be in 2 cells - lets say one date and time in Cell A1 and the second date and time in B1

30/06/2019 15:00:00 A1
01/08/2019 19:30:00 B1

B1 less A1

 

[marcidee]

I have used this for point 8

=MOD(E9-C9,1)*24

I can't work out how you have got the final result for point 9?

 

[Rick Rothstein]

thank you for this - the dates would be in 2 cells - lets say one date and time in Cell A1 and the second date and time in B1

30/06/2019 15:00:00 A1
01/08/2019 19:30:00 B1

B1 less A1

In that case, try this...

=TEXT(B1-A1,"d ""day(s) ""h"" hour(s) ""m"" minute(s)""")

 

[marcidee]

That looks perfect - thank you