I have a spreadsheet that has stumped me maybe someone can help.

The spreadsheet deals with videos and has an extra unit for hours, minutes, seconds and
frames. The last frame is always zero zero as per

01/10/2015 00:00:00:00 00:00:46:00 BEAUTIFUL MUSIC BEAUTIFUL WORLD

Consisting of date, start time, playing time (0hours,0minutes, 46seconds and 00 frames for the title beautiful World.

I wish to import a file which will convert the 00:00:46:00 to digital minutes.
I have tried
=(HOUR(RC[-5]) * 60 + MINUTE(RC[-5]) + SECOND(RC[-5]) / 60)/60 but it does not work?

The field is formatted as a 'general' format at source



Any help appreciated

regards
ogmium

Assuming the 00:00:46:00 is in C1, you could use a formula like:
=LEFT(C1,8)*1440

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thank you for a good try. That equals 24 - the correct answer is 0.76 so thats not right I'm afraid

I am looking for digital minutes from the equation. The spreadsheet looks like this and is 60,000 lines long

0 01/10/2015 00:00:00:00 00:00:46:00 BEAUTIFUL MUSIC BEAUTIFUL
1 01/10/2015 00:00:46:00 00:03:03:00 Gone But Not Forgotten
2 01/10/2015 00:03:49:00 00:05:24:00 Why She Swallows Bullets
3 01/10/2015 00:09:13:00 00:06:21:00 Waltz of the Piano No.3
4 01/10/2015 00:15:34:00 00:00:07:00 LITTLE LINK Watching 1080p DE
5 01/10/2015 00:15:41:00 00:03:58:00 Waterfalls
6 01/10/2015 00:19:39:00 00:03:08:00 Xerxes - largo
7 01/10/2015 00:22:47:00 00:03:23:00 Overcome
8 01/10/2015 00:26:10:00 00:00:10:00 STING - HORNS INTO FULL
9 01/10/2015 00:26:20:00 00:01:30:00 MUSIC PROFILE David Lanz - Ra
10 01/10/2015 00:27:50:00 00:00:10:00 ADVERTS INTRO - DEBUSSEY

The column number starts C3


Nick

Nick:
How about you supplying the above 11 lines in an Excel spreadsheet along with the expected results for each.
Everybody will then be dealing with the same thing, know exactly what's what, and be able to test and verify prior to posting.

To me macropod's formula works like a charm.

OK good idea here is the spreadsheet at http://www.landscapehd.com/download/...RTER_2015.xlsx right click save as

This isnt downloading can someone tell me how to post a spreadsheet that is over the 500Mb size thank you

Nick

@ xor
for me too, that's why I would like to see what it's being applied against.

@ ogmium
you only need to supply a few lines of the file pertaining to what you've asked.
a 500Mb xlsx file sounds 'a little much' to me.
To attach a file to a post, click Go Advanced, scroll down and click Manage Attachments
there is a list of max file size allowed

Attached file

Unfortunately the import file has the four fields in D3

Nick

Attached Files

blank.xlsx (9.9 KB, 13 views)

The formulae works for three fields but not four

I'd have to question some of your 'should be' values.

Attached Files

blank.xlsx (11.5 KB, 12 views)

Sorry made some errors manually

regards

Nick

Attached Files

blank.xlsx (9.9 KB, 11 views)

Let me check that

Once you adjust the formula I posted for your actual cell references (e.g. =LEFT(D3,8)*1440), it returns the correct results in all cases. You need to adjust your expectations - 46 seconds is 0.77 minutes, not 0.76 minutes.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thank you very much for your assistance it works brilliantly

Nick