Duration between two US dates in UK format...

[a5972s]

How on earth do you work out the duration in hours and minutes between two start and end times in US format using excel in UK format? My brain hurts!

I'm having to convert the dates first into UK then use the formula =(B1-A1)*1440/60.

Is there a simpler way?

05/23/2020 21:15 & 05/25/2020 08:33

 

[sandy666]

you can try with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    DTL1 = Table.TransformColumnTypes(Source, {{"StartDate", type datetime}}, "en-US"),
    DTL2 = Table.TransformColumnTypes(DTL1, {{"EndDate", type datetime}}, "en-US"),
    Subtract = Table.AddColumn(DTL2, "Subtraction", each [EndDate] - [StartDate], type duration),
    Thours = Table.AddColumn(Subtract, "Total Hours", each Duration.TotalHours([Subtraction]), type number),
    Round = Table.TransformColumns(Thours,{{"Total Hours", each Number.Round(_, 0), type number}}),
    Minutes = Table.AddColumn(Round, "Minutes", each Duration.Minutes([Subtraction]), Int64.Type)
in
    Minutes

 

[Dave Patton]

You can use Data Text to Columns

The following uses your data. It shows the result of the Data Text to Column. N.B. use Date MDY

T202005c.xlsm
	A	B	C	D	E	F	G
5
6	23-05-20	21:15	23-05-20 21:15	25-05-20	8:33	25-05-20 8:33	35.3
7	23-05-20	21:15	23-05-20 21:15	25-05-20	8:33		35.3
3a

Cell Formulas
Range	Formula
F6,C6:C7	F6	=D6+E6
G6	G6	=(F6-C6)*1440/60
G7	G7	=((D7+E7)-(A7+B7))*24

T202005c.xlsm
	A	B	C	D	E
6	23-05-20	21:15	25-05-20	8:33	35.3
7	23-05-20	21:15	25-05-20	8:33	35:18
3a

Cell Formulas
Range	Formula
E6	E6	=((C6+D6)-(A6+B6))*24
E7	E7	=((C7+D7)-(A7+B7))