How can I convert "2015:12:25 14:01:38" and "2015:12:25 14:43:31" (format: ccyy:mm:dd hh:mm:sec) to a serial numbers?

How do I compare the resulting serial numbers to find the lowest serial number?

GARYWC wrote:

How can I convert "2015:12:25 14:01:38" and "2015:12:25 14:43:31"
(format: ccyy:mm:dd hh:mm:sec) to a serial numbers?

How do I compare the resulting serial numbers to find the lowest serial
number?

First explain what you want when you say "serial number".

As for finding the lowest, you could possibly use the MIN or MINA functions,
depending on your needs.

--
That's the problem with nature, something's always stinging you or
oozing mucous all over you. Let's go and watch TV.
-- Calvin

I suppose "serial date-time" is the correct term.

The serial date-time is the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day.

Its format of the serial date-time is: ddddd.tttttt

"GARYWC" wrote in message
...
How can I convert "2015:12:25 14:01:38" and "2015:12:25 14:43:31" (format:
ccyy:mm:dd hh:mm:sec) to a serial numbers?

How do I compare the resulting serial numbers to find the lowest serial
number?

The term I think you are looking is date (or time) value

First question is are you quite sure your you dates are not already values,
but if strings try something like this

Function DateToValue(DateToConvert)
Dim arr

On Error GoTo errExit

If VarType(DateToConvert) = vbString Then
arr = Split(DateToConvert, " ")
arr(0) = Replace(arr(0), ":", "/")
DateToValue = CDate(arr(0)) + CDate(arr(1))
Else
DateToValue = CDate(DateToConvert)
End If
Exit Function

errExit:
DateToValue = CVErr(xlErrValue)

End Function

The colon separates in the date part are unusual, normally a / or -
If the destination is cells you could apply a number-format to suit, eg
"NumberFormat

You could use this function as a UDF and use Excel's MIN function, or maybe
pass a range of dates, eg

Function MinDate(DateRange As Range) As Date
Dim cel As Range
Dim dtMin As Date
Dim vdtRet As Date

dtMin = #1/1/3000#
For Each cel In DateRange
dtRet = DateToValue(cel)

If dtRet < dtMin Then
dtMin = dtRet
End If
Next

MinDate = dtMin
End Function

As written each cell should contain a date in the expected format, and no
emtpy cells

Peter T

The cells have a text format.

also,

With Range("B1")
..Formula = "=DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))+RIGHT(A 1,8)"
..NumberFormat = "General"
'or
'.NumberFormat = "yyyy/mm/dd hh:mm:ss"
End With

isabelle

Le 2016-11-04 Ã* 10:59, GARYWC a écrit :
The cells have a text format.

Hi,

to transform a (text)date_time in real date_time
=DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))+RIGHT(A1 ,8)
and change the format of the cell to the general format

isabelle

Le 2016-11-04 Ã* 10:59, GARYWC a écrit :
The cells have a text format.

if you don't want the formula but only the value

With Range("B1")
..Value = DateSerial(Left(Cells(1, 1), 4), Mid(Cells(1, 1), 6, 2), Mid(Cells(1,
1), 9, 2)) + CDate(Right(Cells(1, 1), 8))
..NumberFormat = "General"
'or
'.NumberFormat = "yyyy/mm/dd hh:mm:ss"
End With

isabelle

Le 2016-11-04 Ã* 11:51, isabelle a écrit :
also,

With Range("B1")
.Formula = "=DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))+RIGHT(A 1,8)"
.NumberFormat = "General"
'or
'.NumberFormat = "yyyy/mm/dd hh:mm:ss"
End With

isabelle

Le 2016-11-04 Ã* 10:59, GARYWC a écrit :
The cells have a text format.

How can I convert serial number 42361.53146 to a date and time?

Le 2016-11-04 Ã* 13:57, GARYWC a écrit :
How can I convert serial number 42361.53146 to a date and time?

Range("B1").NumberFormat = "yyyy/mm/dd hh:mm:ss"

isabelle

What is the formula when the serial number is in R2?

Range("B1").NumberFormat = "yyyy/mm/dd hh:mm:ss"

replace B1 by R2
so...
Range("R2").NumberFormat = "yyyy/mm/dd hh:mm:ss"

isabelle

Le 2016-11-04 Ã* 15:15, GARYWC a écrit :
What is the formula when the serial number is in R2?


Le 2016-11-04 Ã* 14:44, isabelle a écrit :

Le 2016-11-04 Ã* 13:57, GARYWC a écrit :
How can I convert serial number 42361.53146 to a date and time?

Range("B1").NumberFormat = "yyyy/mm/dd hh:mm:ss"

isabelle

Most of the 4,118 images were taken during tours of Italy, Spain, Eastern Europe and the southwest U.S.A.

Using EXIFTOOL, I extracted, for each image-file, these dates/times:

FileModifyDate
FileAccessDate
FileCreateDate
ModifyDate
DateTimeOriginal
CreateDate

I then converted each date/time into a serial number (for example: 39726.53803) and determined which serial number for each image was lowest.

I then sorted the serial numbers into ascending order so the images are in chronological order to match the trips' itineraries.

I can now identify the location and subject of each of the photos.