So I have data that looks like this:
+00:00, 2:02:57
+00:16, 2:03:13
+02:59, 2:05:56
+03:42, 2:06:39
+04:31, 2:07:28
+04:38, 2:07:35
I'd like to return both columns in seconds like:
0, 7377
16, 7393
179, 7556
etc...
There are no commas...I just did that to signify that there are two columns of data (so I'll need 2 different functions).
The first column will always have a + sign in the beginning if that matters.
Thanks!
Last edited by gannon_w; 10-29-2014 at 08:59 AM.
Hi,
for the 1st column
=VALUE(SUBSTITUTE(A1,"+","00:"))*24*60*60
for the second column
=B1*24*60*60
Remeber to format the cells as general.
Rule 1: Never merge cells
Rule 2: See rule 1
"Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".
One formula, will work for both columns:
Formula:
Please Login or Register to view this content.
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
=(hour(A1)*3600)+(minute(A1)*60)+second(A1)
Hi,
for the 1st column
=VALUE(SUBSTITUTE(A1,"+","00:"))*24*60*60
for the second column
=B1*24*60*60
Remeber to format the cells as general.
All solutions worked...I've added to your reputations!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks