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
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Column1.1​

[/TD]
[TD]

Column1.2​

[/TD]
[TD]

Difference​

[/TD]
[TD]

Days​

[/TD]
[TD]

Hours​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

07/31/2019 15:00:00​

[/TD]
[TD]

08/01/2019 16:00:00​

[/TD]
[TD]

1.041666667​

[/TD]
[TD]

1​

[/TD]
[TD]

1:00​

[/TD]
[/TR]
</tbody>[/TABLE]


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