Time formulas
 

After importing them from camera to PC I always rename my photos and
movie clips with a prefix that shows the date and time, in the format
YYMMDD-hhmmss. This allows convenient sorting and placement when I use
them for making my family DVDs.

Using Excel 2000 I'm now trying to calculate the time gap between
several hundred of these, as shown he

https://dl.dropboxusercontent.com/u/...Formulas-1.jpg

(The photos are from a city bus tour in Madrid. The time gaps, used
with a bus timetable, will help me identify their location on the
route. My next camera must have GPS functionality!)

Q1: Could I get some help on how to build those two formulas please?

Q2: Prompted by Claus's help recently, I'm also trying to re-learn a
bit of the little I once knew about using Excel VBA. I'm starting with
recording (as I baulk at VBA coding, apart from copy/pasting and
simple edits). But so far my efforts to record this have failed.
Perhaps this is a macro that CANNOT be recorded? If not, how would I
proceed please?

I can do it with Macro Express Pro but I'd like to learn how to
achieve it in Excel please.


--
Terry, East Grinstead, UK

Time formulas
 

Hi Terry,

Am Mon, 07 Sep 2015 08:31:41 +0100 schrieb Terry Pinnell:

Using Excel 2000 I'm now trying to calculate the time gap between
several hundred of these, as shown he

https://dl.dropboxusercontent.com/u/...Formulas-1.jpg

in B2 try:
=TIME(MID(A2,FIND("-",A2)+1,2),MID(A2,FIND("-",A2)+3,2),MID(A2,FIND("-",A2)+5,2))
and copy down
in C3 try:
=B3-B2


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Time formulas
 

Hi Terry,

Am Mon, 7 Sep 2015 10:59:48 +0200 schrieb Claus Busch:

in B2 try:
=TIME(MID(A2,FIND("-",A2)+1,2),MID(A2,FIND("-",A2)+3,2),MID(A2,FIND("-",A2)+5,2))

so the part in front of the hyphen is a date and has always 8 digits you
can use in B2:
=TIME(MID(A2,10,2),MID(A2,12,2),MID(A2,14,2))


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Time formulas
 

hi again,

Am Mon, 7 Sep 2015 11:03:40 +0200 schrieb Claus Busch:

so the part in front of the hyphen is a date and has always 8 digits you
can use in B2:
=TIME(MID(A2,10,2),MID(A2,12,2),MID(A2,14,2))

or without helper column for the time in B3:
=TIME(MID(A3,10,2),MID(A3,12,2),MID(A3,14,2))-TIME(MID(A2,10,2),MID(A2,12,2),MID(A2,14,2))
and copy down.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Time formulas
 

Claus Busch wrote:

hi again,

Am Mon, 7 Sep 2015 11:03:40 +0200 schrieb Claus Busch:

so the part in front of the hyphen is a date and has always 8 digits you
can use in B2:
=TIME(MID(A2,10,2),MID(A2,12,2),MID(A2,14,2))

or without helper column for the time in B3:
=TIME(MID(A3,10,2),MID(A3,12,2),MID(A3,14,2))-TIME(MID(A2,10,2),MID(A2,12,2),MID(A2,14,2))
and copy down.


Regards
Claus B.

Excellent, all working, thanks Claus.

--
Terry, East Grinstead, UK