PowerQuery, does not add 1 to next day time

[lezawang]

Hi
I have StartTime and EndTime columns. I want to subtract End-Start and put the result in a new columns. So I used PowerQuery and I inserted a new columns with the following formula

= Table.AddColumn(#"Changed Type", "Custom", each [#"End-Time"]-[#"Start-Time"])

but to my surprise, Power Query can not do (next day time) thing. If the start was 10:00PM and the End is the next day 2:00am, power Query wont give you 4 hrs. See below please. Can that be fixed in PowerQuery. I know how to do a formula in Excel but I want to do it in PowerQ. Thank you very much.

Book5 (1).xlsx
	A	B	C	D	E	F
1	Full Name	Dept	Start-Time	End-Time
2	Oliver Olivier	Accounting	7:33 PM	8:30 AM
3	Anthony Ellwood	IT	2:25 PM	4:22 PM
4	Doug Anderson	Human Resources	5:34 AM	12:34 PM
5	Danielle Mcneill	IT	1:31 AM	1:12 PM
6	Elena Clifton	Human Resources	3:21 AM	10:53 PM
7	Angelica Johnson	Operations	2:41 PM	10:04 AM
8	Camellia Morris	Marketing	2:20 PM	11:57 AM
9	Domenic Plant	Operations	8:55 AM	12:02 AM
10	Manuel Jones	Management	9:13 PM	7:09 PM
11	Johnathan Wood	Operations	6:58 PM	11:35 AM
12	Sharon Upsdell	IT	11:05 AM	2:49 PM
13	Barry Hill	Marketing	8:26 AM	5:03 PM
14
15
16	Full Name	Dept	Start-Time	End-Time	Custom
17	Oliver Olivier	Accounting	7:33:00 PM	8:30:00 AM	-0.460416667
18	Anthony Ellwood	IT	2:25:00 PM	4:22:00 PM	0.08125
19	Doug Anderson	Human Resources	5:34:00 AM	12:34:00 PM	0.291666667
20	Danielle Mcneill	IT	1:31:00 AM	1:12:00 PM	0.486805556
21	Elena Clifton	Human Resources	3:21:00 AM	10:53:00 PM	0.813888889
22	Angelica Johnson	Operations	2:41:00 PM	10:04:00 AM	-0.192361111
23	Camellia Morris	Marketing	2:20:00 PM	11:57:00 AM	-0.099305556
24	Domenic Plant	Operations	8:55:00 AM	12:02:00 AM	-0.370138889
25	Manuel Jones	Management	9:13:00 PM	7:09:00 PM	-0.086111111
26	Johnathan Wood	Operations	6:58:00 PM	11:35:00 AM	-0.307638889
27	Sharon Upsdell	IT	11:05:00 AM	2:49:00 PM	0.155555556
28	Barry Hill	Marketing	8:26:00 AM	5:03:00 PM	0.359027778
29
Sheet1

 

[Saurabhj]

Hi, I used below and getting required result.

= Table.AddColumn(#"Changed Type2", "Date Difference", each Duration.Hours([#"Start-Time"]-[#"End-Time"]))

(See Screenshot)

 

[Alex Blakenburg]

Under add columns make sure you use the Time Subtraction.

 

[lezawang]

Under add columns make sure you use the Time Subtraction.

View attachment 40597

Thank you all, I am trying to do that but it is grayed out. I first clicked on Add Column tab in PowerQ then I click on Custom Column then I changed the name of the column and under Custom Column formula, I clicked on EndTime column and then entered - and then clicked on StartTime column and then clicked ok. When I should click on Time then Subtract? Thank you very much.

 

[Alex Blakenburg]

1. Select End Time column
2. Ctrl + Select Start Time column
3. Select Add Column on menu
4. Select Time on the menu
5. Select subtract on the Time drop down menu

 

[lezawang]

wow brilliant. Thank you all so much.

 

[Alex Blakenburg]

No problem. Glad we could help.