Time Difference Calucation

surd80

New Member
Joined
Jan 26, 2016
Messages
4
I have 2 Column sheet with different formatting. I need to calculate the time difference. Thanks

Time Diference.xlsx
AB
1Ticket Opened DateResolved Date Time
230/06/2020 06:25 PM2/7/2020 16:55
330/06/2020 04:40 PM30/06/2020 06:16 PM
430/06/2020 12:20 PM30/06/2020 04:32 PM
530/06/2020 11:52 AM30/06/2020 06:39 PM
630/06/2020 11:57 AM30/06/2020 11:57 AM
730/06/2020 11:28 AM30/06/2020 05:50 PM
830/06/2020 10:51 AM30/06/2020 11:54 AM
930/06/2020 10:34 AM30/06/2020 10:35 AM
1030/06/2020 10:11 AM30/06/2020 08:55 PM
1129/06/2020 01:34 PM30/06/2020 03:54 PM
1229/06/2020 01:27 PM29/06/2020 06:02 PM
1329/06/2020 01:22 PM30/06/2020 02:50 PM
1429/06/2020 12:34 PM29/06/2020 06:06 PM
1529/06/2020 11:35 AM29/06/2020 11:35 AM
1629/06/2020 12:16 AM29/06/2020 03:43 PM
1728/06/2020 03:10 AM5/7/2020 15:16
1827/06/2020 03:23 PM30/06/2020 10:35 PM
1926/06/2020 12:21 PM29/06/2020 03:18 PM
2026/06/2020 12:04 PM27/06/2020 05:02 PM
2126/06/2020 12:02 PM29/06/2020 01:43 PM
2226/06/2020 11:26 AM30/06/2020 11:49 AM
2325/06/2020 04:52 PM26/06/2020 12:15 PM
2425/06/2020 03:40 PM1/7/2020 14:55
2524/06/2020 12:54 PM24/06/2020 04:53 PM
2624/06/2020 12:04 PM25/06/2020 02:46 PM
2724/06/2020 11:31 AM24/06/2020 11:32 AM
2824/06/2020 11:00 AM24/06/2020 11:26 AM
2924/06/2020 10:10 AM24/06/2020 04:51 PM
3023/06/2020 04:04 PM24/06/2020 02:41 PM
3123/06/2020 03:17 PM23/06/2020 03:18 PM
3223/06/2020 12:20 PM23/06/2020 06:38 PM
3323/06/2020 11:44 AM25/06/2020 02:48 PM
3423/06/2020 11:21 AM2/7/2020 13:52
3523/06/2020 09:39 AM23/06/2020 06:35 PM
3622/06/2020 11:17 AM22/06/2020 03:47 PM
3722/06/2020 10:48 AM22/06/2020 11:07 AM
3819/06/2020 12:55 PM20/06/2020 03:24 PM
3919/06/2020 12:06 PM23/06/2020 05:44 PM
4019/06/2020 11:36 AM19/06/2020 11:37 AM
4119/06/2020 10:58 AM19/06/2020 03:30 PM
4219/06/2020 07:23 AM19/06/2020 07:24 AM
4318/06/2020 01:09 PM18/06/2020 02:16 PM
4418/06/2020 11:05 AM18/06/2020 11:07 AM
4517/06/2020 04:21 PM18/06/2020 04:37 PM
4617/06/2020 12:00 PM17/06/2020 12:01 PM
4716/06/2020 05:13 PM17/06/2020 11:23 AM
4815/06/2020 09:32 PM16/06/2020 10:01 AM
4915/06/2020 12:29 PM15/06/2020 12:58 PM
5012/6/2020 20:2715/06/2020 10:17 AM
5112/6/2020 12:3715/06/2020 10:18 AM
5211/6/2020 17:0215/06/2020 11:33 AM
5311/6/2020 15:4617/06/2020 06:01 PM
5411/6/2020 14:4412/6/2020 12:44
5511/6/2020 14:0116/06/2020 12:24 PM
5611/6/2020 11:4215/06/2020 03:24 PM
5710/6/2020 18:3010/6/2020 23:12
5810/6/2020 15:08
5910/6/2020 14:5211/6/2020 10:22
6010/6/2020 14:1710/6/2020 14:57
619/6/2020 18:0111/6/2020 10:12
629/6/2020 17:249/6/2020 17:39
639/6/2020 0:319/6/2020 9:57
648/6/2020 15:558/6/2020 15:56
658/6/2020 10:259/6/2020 15:52
665/6/2020 14:006/6/2020 12:19
675/6/2020 10:026/6/2020 12:23
684/6/2020 16:25
694/6/2020 12:404/6/2020 12:41
704/6/2020 12:114/6/2020 13:52
714/6/2020 10:294/6/2020 10:31
724/6/2020 10:234/6/2020 12:24
734/6/2020 9:564/6/2020 9:56
743/6/2020 10:053/6/2020 11:11
752/6/2020 20:163/6/2020 11:13
762/6/2020 11:004/6/2020 15:36
772/6/2020 10:402/6/2020 12:57
781/6/2020 23:382/6/2020 11:08
791/6/2020 21:592/6/2020 16:39
801/6/2020 19:512/6/2020 14:24
811/6/2020 19:54
821/6/2020 11:44
Difference
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,055
like this?
format: d.hh:mm
Ticket Opened DateResolved Date TimeSubtraction
30/06/2020 18:2502/07/2020 16:551.22:30:00
30/06/2020 16:4030/06/2020 18:160.01:36:00
30/06/2020 12:2030/06/2020 16:320.04:12:00
30/06/2020 11:5230/06/2020 18:390.06:47:00
30/06/2020 11:5730/06/2020 11:570.00:00:00
30/06/2020 11:2830/06/2020 17:500.06:22:00
30/06/2020 10:5130/06/2020 11:540.01:03:00
30/06/2020 10:3430/06/2020 10:350.00:01:00
30/06/2020 10:1130/06/2020 20:550.10:44:00
29/06/2020 13:3430/06/2020 15:541.02:20:00
29/06/2020 13:2729/06/2020 18:020.04:35:00
29/06/2020 13:2230/06/2020 14:501.01:28:00
29/06/2020 12:3429/06/2020 18:060.05:32:00
29/06/2020 11:3529/06/2020 11:350.00:00:00
29/06/2020 00:1629/06/2020 15:430.15:27:00
28/06/2020 03:1005/07/2020 15:167.12:06:00
27/06/2020 15:2330/06/2020 22:353.07:12:00
26/06/2020 12:2129/06/2020 15:183.02:57:00
26/06/2020 12:0427/06/2020 17:021.04:58:00
26/06/2020 12:0229/06/2020 13:433.01:41:00
26/06/2020 11:2630/06/2020 11:494.00:23:00
25/06/2020 16:5226/06/2020 12:150.19:23:00
25/06/2020 15:4001/07/2020 14:555.23:15:00
24/06/2020 12:5424/06/2020 16:530.03:59:00
24/06/2020 12:0425/06/2020 14:461.02:42:00
24/06/2020 11:3124/06/2020 11:320.00:01:00
24/06/2020 11:0024/06/2020 11:260.00:26:00
24/06/2020 10:1024/06/2020 16:510.06:41:00
23/06/2020 16:0424/06/2020 14:410.22:37:00
23/06/2020 15:1723/06/2020 15:180.00:01:00
23/06/2020 12:2023/06/2020 18:380.06:18:00
23/06/2020 11:4425/06/2020 14:482.03:04:00
23/06/2020 11:2102/07/2020 13:529.02:31:00
23/06/2020 09:3923/06/2020 18:350.08:56:00
22/06/2020 11:1722/06/2020 15:470.04:30:00
22/06/2020 10:4822/06/2020 11:070.00:19:00
19/06/2020 12:5520/06/2020 15:241.02:29:00
19/06/2020 12:0623/06/2020 17:444.05:38:00
19/06/2020 11:3619/06/2020 11:370.00:01:00
19/06/2020 10:5819/06/2020 15:300.04:32:00
19/06/2020 07:2319/06/2020 07:240.00:01:00
18/06/2020 13:0918/06/2020 14:160.01:07:00
18/06/2020 11:0518/06/2020 11:070.00:02:00
17/06/2020 16:2118/06/2020 16:371.00:16:00
17/06/2020 12:0017/06/2020 12:010.00:01:00
16/06/2020 17:1317/06/2020 11:230.18:10:00
15/06/2020 21:3216/06/2020 10:010.12:29:00
15/06/2020 12:2915/06/2020 12:580.00:29:00
12/06/2020 20:2715/06/2020 10:172.13:50:00
12/06/2020 12:3715/06/2020 10:182.21:41:00
11/06/2020 17:0215/06/2020 11:333.18:31:00
11/06/2020 15:4617/06/2020 18:016.02:15:00
11/06/2020 14:4412/06/2020 12:440.22:00:00
11/06/2020 14:0116/06/2020 12:244.22:23:00
11/06/2020 11:4215/06/2020 15:244.03:42:00
10/06/2020 18:3010/06/2020 23:120.04:42:00
10/06/2020 15:08
10/06/2020 14:5211/06/2020 10:220.19:30:00
10/06/2020 14:1710/06/2020 14:570.00:40:00
09/06/2020 18:0111/06/2020 10:121.16:11:00
09/06/2020 17:2409/06/2020 17:390.00:15:00
09/06/2020 00:3109/06/2020 09:570.09:26:00
08/06/2020 15:5508/06/2020 15:560.00:01:00
08/06/2020 10:2509/06/2020 15:521.05:27:00
05/06/2020 14:0006/06/2020 12:190.22:19:00
05/06/2020 10:0206/06/2020 12:231.02:21:00
04/06/2020 16:25
04/06/2020 12:4004/06/2020 12:410.00:01:00
04/06/2020 12:1104/06/2020 13:520.01:41:00
04/06/2020 10:2904/06/2020 10:310.00:02:00
04/06/2020 10:2304/06/2020 12:240.02:01:00
04/06/2020 09:5604/06/2020 09:560.00:00:00
03/06/2020 10:0503/06/2020 11:110.01:06:00
02/06/2020 20:1603/06/2020 11:130.14:57:00
02/06/2020 11:0004/06/2020 15:362.04:36:00
02/06/2020 10:4002/06/2020 12:570.02:17:00
01/06/2020 23:3802/06/2020 11:080.11:30:00
01/06/2020 21:5902/06/2020 16:390.18:40:00
01/06/2020 19:5102/06/2020 14:240.18:33:00
01/06/2020 19:54
01/06/2020 11:44
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,868
Office Version
  1. 365
Platform
  1. Windows
or perhaps

Book1
ABC
1Ticket Opened DateResolved Date TimeResult
230/06/2020 18:25:0002/07/2020 16:55:0046 hours 30 minutes
330/06/2020 16:40:0030/06/2020 18:16:001 hours 36 minutes
430/06/2020 12:20:0030/06/2020 16:32:004 hours 12 minutes
530/06/2020 11:52:0030/06/2020 18:39:006 hours 47 minutes
630/06/2020 11:57:0030/06/2020 11:57:000 hours 0 minutes
730/06/2020 11:28:0030/06/2020 17:50:006 hours 22 minutes
830/06/2020 10:51:0030/06/2020 11:54:001 hours 3 minutes
930/06/2020 10:34:0030/06/2020 10:35:000 hours 1 minutes
1030/06/2020 10:11:0030/06/2020 20:55:0010 hours 44 minutes
1129/06/2020 13:34:0030/06/2020 15:54:0026 hours 20 minutes
1229/06/2020 13:27:0029/06/2020 18:02:004 hours 35 minutes
1329/06/2020 13:22:0030/06/2020 14:50:0025 hours 28 minutes
1429/06/2020 12:34:0029/06/2020 18:06:005 hours 32 minutes
1529/06/2020 11:35:0029/06/2020 11:35:000 hours 0 minutes
1629/06/2020 00:16:0029/06/2020 15:43:0015 hours 27 minutes
1728/06/2020 03:10:0005/07/2020 15:16:00180 hours 6 minutes
1827/06/2020 15:23:0030/06/2020 22:35:0079 hours 12 minutes
1926/06/2020 12:21:0029/06/2020 15:18:0074 hours 57 minutes
2026/06/2020 12:04:0027/06/2020 17:02:0028 hours 58 minutes
2126/06/2020 12:02:0029/06/2020 13:43:0073 hours 41 minutes
2226/06/2020 11:26:0030/06/2020 11:49:0096 hours 23 minutes
2325/06/2020 16:52:0026/06/2020 12:15:0019 hours 23 minutes
2425/06/2020 15:40:0001/07/2020 14:55:00143 hours 15 minutes
2524/06/2020 12:54:0024/06/2020 16:53:003 hours 59 minutes
2624/06/2020 12:04:0025/06/2020 14:46:0026 hours 42 minutes
2724/06/2020 11:31:0024/06/2020 11:32:000 hours 1 minutes
2824/06/2020 11:00:0024/06/2020 11:26:000 hours 26 minutes
2924/06/2020 10:10:0024/06/2020 16:51:006 hours 41 minutes
3023/06/2020 16:04:0024/06/2020 14:41:0022 hours 37 minutes
3123/06/2020 15:17:0023/06/2020 15:18:000 hours 1 minutes
3223/06/2020 12:20:0023/06/2020 18:38:006 hours 18 minutes
3323/06/2020 11:44:0025/06/2020 14:48:0051 hours 4 minutes
3423/06/2020 11:21:0002/07/2020 13:52:00218 hours 31 minutes
3523/06/2020 09:39:0023/06/2020 18:35:008 hours 56 minutes
3622/06/2020 11:17:0022/06/2020 15:47:004 hours 30 minutes
3722/06/2020 10:48:0022/06/2020 11:07:000 hours 19 minutes
3819/06/2020 12:55:0020/06/2020 15:24:0026 hours 29 minutes
3919/06/2020 12:06:0023/06/2020 17:44:00101 hours 38 minutes
4019/06/2020 11:36:0019/06/2020 11:37:000 hours 1 minutes
4119/06/2020 10:58:0019/06/2020 15:30:004 hours 32 minutes
4219/06/2020 07:23:0019/06/2020 07:24:000 hours 1 minutes
4318/06/2020 13:09:0018/06/2020 14:16:001 hours 7 minutes
4418/06/2020 11:05:0018/06/2020 11:07:000 hours 2 minutes
4517/06/2020 16:21:0018/06/2020 16:37:0024 hours 16 minutes
4617/06/2020 12:00:0017/06/2020 12:01:000 hours 1 minutes
4716/06/2020 17:13:0017/06/2020 11:23:0018 hours 10 minutes
4815/06/2020 21:32:0016/06/2020 10:01:0012 hours 29 minutes
4915/06/2020 12:29:0015/06/2020 12:58:000 hours 29 minutes
5012/06/2020 20:27:0015/06/2020 10:17:0061 hours 50 minutes
5112/06/2020 12:37:0015/06/2020 10:18:0069 hours 41 minutes
5211/06/2020 17:02:0015/06/2020 11:33:0090 hours 31 minutes
5311/06/2020 15:46:0017/06/2020 18:01:00146 hours 15 minutes
5411/06/2020 14:44:0012/06/2020 12:44:0022 hours 0 minutes
5511/06/2020 14:01:0016/06/2020 12:24:00118 hours 23 minutes
5611/06/2020 11:42:0015/06/2020 15:24:0099 hours 42 minutes
5710/06/2020 18:30:0010/06/2020 23:12:004 hours 42 minutes
5810/06/2020 15:08:00 
5910/06/2020 14:52:0011/06/2020 10:22:0019 hours 30 minutes
6010/06/2020 14:17:0010/06/2020 14:57:000 hours 40 minutes
6109/06/2020 18:01:0011/06/2020 10:12:0040 hours 11 minutes
6209/06/2020 17:24:0009/06/2020 17:39:000 hours 15 minutes
6309/06/2020 00:31:0009/06/2020 09:57:009 hours 26 minutes
6408/06/2020 15:55:0008/06/2020 15:56:000 hours 1 minutes
6508/06/2020 10:25:0009/06/2020 15:52:0029 hours 27 minutes
6605/06/2020 14:00:0006/06/2020 12:19:0022 hours 19 minutes
6705/06/2020 10:02:0006/06/2020 12:23:0026 hours 21 minutes
6804/06/2020 16:25:00 
6904/06/2020 12:40:0004/06/2020 12:41:000 hours 1 minutes
7004/06/2020 12:11:0004/06/2020 13:52:001 hours 41 minutes
7104/06/2020 10:29:0004/06/2020 10:31:000 hours 2 minutes
7204/06/2020 10:23:0004/06/2020 12:24:002 hours 1 minutes
7304/06/2020 09:56:0004/06/2020 09:56:000 hours 0 minutes
7403/06/2020 10:05:0003/06/2020 11:11:001 hours 6 minutes
7502/06/2020 20:16:0003/06/2020 11:13:0014 hours 57 minutes
7602/06/2020 11:00:0004/06/2020 15:36:0052 hours 36 minutes
7702/06/2020 10:40:0002/06/2020 12:57:002 hours 17 minutes
7801/06/2020 23:38:0002/06/2020 11:08:0011 hours 30 minutes
7901/06/2020 21:59:0002/06/2020 16:39:0018 hours 40 minutes
8001/06/2020 19:51:0002/06/2020 14:24:0018 hours 33 minutes
8101/06/2020 19:54:00 
8201/06/2020 11:44:00 
Sheet7
Cell Formulas
RangeFormula
C2:C82C2=IF(B2>=A2,B2-A2,"")


Custom Format

[h]" hours "m" minutes"
Custom Format Time Diff.jpg
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,868
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Just spotted your last post
- amend my suggested format to include seconds
[h]" hours "m" minutes " s " seconds"

or simply ...
[h]:mm:ss
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,055

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
408
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web

ADVERTISEMENT

thanks Yongle for your support
 

Watch MrExcel Video

Forum statistics

Threads
1,112,782
Messages
5,542,493
Members
410,557
Latest member
chidambaramseetha
Top