Generate difference between the first and last timestamp for each day in a month

S

sidds

New Member
Joined
Aug 23, 2013
Messages
6
Hello Everyone,

I've timestamp data for a list of resources from Oct'14 to date. I need to generate the difference between the first and last timestamp for a day. Please see an example below.

I'll like the result in hours as:
Jun 1 2015: 6.5
Jun 2 2015: 8
and so on
Timestamp
Date
12:24:54:000PMJun 1 2015
12:24:57:000PMJun 1 2015
12:25:00:000PMJun 1 2015
12:25:03:000PMJun 1 2015
12:35:01:000PMJun 1 2015
1:20:37:000PMJun 1 2015
2:01:38:000PMJun 1 2015
2:01:38:000PMJun 1 2015
5:34:38:000PMJun 1 2015
5:34:38:000PMJun 1 2015
9:04:49:000AMJun 2 2015
9:04:49:000AMJun 2 2015
9:16:24:000AMJun 2 2015
9:16:24:000AMJun 2 2015
9:17:51:000AMJun 2 2015
9:17:51:000AMJun 2 2015
1:34:26:000PMJun 2 2015
1:34:26:000PMJun 2 2015
5:39:46:000PMJun 2 2015
5:39:47:000PMJun 2 2015
9:01:14:000AMJun 3 2015
9:01:14:000AMJun 3 2015
10:43:53:000AMJun 3 2015
10:53:24:000AMJun 3 2015
12:38:33:000PMJun 3 2015
12:38:33:000PMJun 3 2015
12:51:03:000PMJun 3 2015
1:10:11:000PMJun 3 2015
1:19:07:000PMJun 3 2015
1:19:08:000PMJun 3 2015
1:26:43:000PMJun 3 2015
2:01:44:000PMJun 3 2015
5:42:31:000PMJun 3 2015
5:42:31:000PMJun 3 2015
9:01:12:000AMJun 4 2015
9:01:12:000AMJun 4 2015
9:57:36:000AMJun 4 2015
10:03:21:000AMJun 4 2015
1:17:14:000PMJun 4 2015
1:17:14:000PMJun 4 2015
1:17:35:000PMJun 4 2015
1:17:35:000PMJun 4 2015
1:26:29:000PMJun 4 2015
1:26:29:000PMJun 4 2015
1:41:33:000PMJun 4 2015
1:41:33:000PMJun 4 2015
3:01:04:000PMJun 4 2015
3:04:33:000PMJun 4 2015
5:49:13:000PMJun 4 2015
5:49:13:000PMJun 4 2015
9:04:09:000AMJun 5 2015
9:04:09:000AMJun 5 2015
9:56:31:000AMJun 5 2015
9:56:31:000AMJun 5 2015
9:57:31:000AMJun 5 2015
9:57:31:000AMJun 5 2015
9:59:58:000AMJun 5 2015
11:47:01:000AMJun 5 2015
11:47:01:000AMJun 5 2015
11:53:20:000AMJun 5 2015
1:01:32:000PMJun 5 2015
1:01:32:000PMJun 5 2015
1:39:10:000PMJun 5 2015
1:57:25:000PMJun 5 2015
2:01:35:000PMJun 5 2015
5:41:53:000PMJun 5 2015
5:41:53:000PMJun 5 2015
9:01:56:000AMJun 8 2015
9:01:56:000AMJun 8 2015
10:23:13:000AMJun 8 2015
10:23:13:000AMJun 8 2015
10:47:56:000AMJun 8 2015
10:47:56:000AMJun 8 2015
12:45:53:000PMJun 8 2015
12:45:53:000PMJun 8 2015
12:47:32:000PMJun 8 2015
1:12:40:000PMJun 8 2015
1:21:54:000PMJun 8 2015
5:54:44:000PMJun 8 2015
5:54:44:000PMJun 8 2015
9:03:06:000AMJun 9 2015
9:03:06:000AMJun 9 2015
11:39:47:000AMJun 9 2015
11:43:01:000AMJun 9 2015
1:07:05:000PMJun 9 2015
1:07:05:000PMJun 9 2015
1:39:30:000PMJun 9 2015
5:46:11:000PMJun 9 2015
5:46:11:000PMJun 9 2015
9:05:01:000AMJun 10 201
9:05:01:000AMJun 10 201
10:09:51:000AMJun 10 201
10:09:51:000AMJun 10 201
10:33:55:000AMJun 10 201
1:05:48:000PMJun 10 201
1:37:41:000PMJun 10 201
5:48:23:000PMJun 10 201
5:48:23:000PMJun 10 201
9:00:04:000AMJun 11 201
10:31:44:000AMJun 11 201
10:44:13:000AMJun 11 201
1:02:49:000PMJun 11 201
1:09:08:000PMJun 11 201
1:10:27:000PMJun 11 201
1:10:27:000PMJun 11 201
1:40:53:000PMJun 11 201
1:40:53:000PMJun 11 201
5:44:34:000PMJun 11 201
5:44:35:000PMJun 11 201
9:41:27:000AMJun 12 201
9:41:27:000AMJun 12 201
1:21:28:000PMJun 12 201
2:08:44:000PMJun 12 201
4:37:57:000PMJun 12 201
4:37:57:000PMJun 12 201
9:12:41:000AMJun 15 201
1:06:45:000PMJun 15 201
1:41:52:000PMJun 15 201
4:39:44:000PMJun 15 201
5:47:13:000PMJun 15 201
5:47:13:000PMJun 15 201
9:01:46:000AMJun 16 201
9:01:46:000AMJun 16 201
10:48:22:000AMJun 16 201
10:48:22:000AMJun 16 201
11:02:25:000AMJun 16 201
11:02:25:000AMJun 16 201
11:41:05:000AMJun 16 201
11:41:05:000AMJun 16 201
11:41:20:000AMJun 16 201
11:41:21:000AMJun 16 201
11:46:39:000AMJun 16 201
11:46:39:000AMJun 16 201
11:46:57:000AMJun 16 201
11:46:57:000AMJun 16 201
1:05:41:000PMJun 16 201
1:42:46:000PMJun 16 201
4:24:26:000PMJun 16 201
5:47:51:000PMJun 16 201
5:47:51:000PMJun 16 201
9:17:45:000AMJun 17 201
10:24:07:000AMJun 17 201
10:36:48:000AMJun 17 201
1:08:54:000PMJun 17 201
1:08:54:000PMJun 17 201
1:55:35:000PMJun 17 201
1:55:35:000PMJun 17 201
4:35:16:000PMJun 17 201
4:51:13:000PMJun 17 201
5:42:54:000PMJun 17 201
5:42:54:000PMJun 17 201
9:12:04:000AMJun 18 201
9:12:04:000AMJun 18 201
1:12:54:000PMJun 18 201
1:30:40:000PMJun 18 201
4:11:21:000PMJun 18 201
4:40:44:000PMJun 18 201
5:49:22:000PMJun 18 201
5:49:22:000PMJun 18 201
9:07:01:000AMJun 19 201
9:07:01:000AMJun 19 201
10:33:42:000AMJun 19 201
10:46:17:000AMJun 19 201
1:04:53:000PMJun 19 201
1:44:33:000PMJun 19 201
2:59:45:000PMJun 19 201
3:27:31:000PMJun 19 201
5:20:16:000PMJun 19 201
5:20:16:000PMJun 19 201
9:29:05:000AMJun 22 201
9:29:06:000AMJun 22 201
10:34:36:000AMJun 22 201
10:37:29:000AMJun 22 201
10:45:29:000AMJun 22 201
1:05:23:000PMJun 22 201
1:16:34:000PMJun 22 201
1:17:20:000PMJun 22 201
1:17:20:000PMJun 22 201
1:46:59:000PMJun 22 201
4:09:01:000PMJun 22 201
4:37:00:000PMJun 22 201
5:42:16:000PMJun 22 201
5:42:16:000PMJun 22 201
9:01:38:000AMJun 23 201
9:01:38:000AMJun 23 201
10:18:13:000AMJun 23 201
10:38:01:000AMJun 23 201
10:38:01:000AMJun 23 201
1:06:20:000PMJun 23 201
1:06:20:000PMJun 23 201
1:46:27:000PMJun 23 201
1:46:27:000PMJun 23 201
5:40:30:000PMJun 23 201
5:40:30:000PMJun 23 201
9:05:04:000AMJun 24 201
9:05:04:000AMJun 24 201
10:28:08:000AMJun 24 201
10:28:08:000AMJun 24 201
10:49:51:000AMJun 24 201
10:49:51:000AMJun 24 201
1:08:18:000PMJun 24 201
1:46:12:000PMJun 24 201

<tbody>
</tbody>

<tbody>
</tbody>
Appreciate any help :)
 
Last edited:

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
Your data isn't recognised by excel as true date and time values so needs a bit of messing around with. However this seems to work. Its an array formula so needs entering with CNTL-SHIFT-ENTER. It assumes your data is pasted into A1

=24*(MAX(IF($B$2:$B$203=B1,SUBSTITUTE($A$2:$A$203,":000"," ")+0))-MIN(IF($B$2:$B$203=B1,SUBSTITUTE($A$2:$A$203,":000"," ")+0)))
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Assign Month number 1 to n based on sequential week range
Replies
6
Views
281
amolvijay
A
M
  • Solved
How to count certain month from a column
Replies
3
Views
160
Joe4
Joe4
J
  • Question
adding cells based on criteria across several columns
Replies
5
Views
143
Cubist
Cubist
S
  • Question
Merge mail on multiple section
Replies
0
Views
132
Salehin31
S
W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
16
Views
1K
phiero21
P

Forum statistics

Threads
1,216,063
Messages
6,128,559
Members
449,458
Latest member
gillmit

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top