Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #109524 > unrolled thread

Convert date/time to serial number and compare serial numbers

Back to article view | Back to microsoft.public.excel.programming

Contents

  Convert date/time to serial number and compare serial numbers GARYWC <gcotterl@earthlink.net> - 2016-11-03 15:35 -0700
    Re: Convert date/time to serial number and compare serial numbers "Auric__" <not.my.real@email.address> - 2016-11-04 04:19 +0000
    Re: Convert date/time to serial number and compare serial numbers GARYWC <gcotterl@earthlink.net> - 2016-11-03 22:05 -0700
    Re: Convert date/time to serial number and compare serial numbers "Peter T" <askformy@gmail.com> - 2016-11-04 08:20 +0000
    Re: Convert date/time to serial number and compare serial numbers GARYWC <gcotterl@earthlink.net> - 2016-11-04 07:59 -0700
      Re: Convert date/time to serial number and compare serial numbers isabelle <i@v.invalid> - 2016-11-04 11:51 -0400
        Re: Convert date/time to serial number and compare serial numbers isabelle <i@v.invalid> - 2016-11-04 12:10 -0400
      Re: Convert date/time to serial number and compare serial numbers isabelle <i@v.invalid> - 2016-11-04 11:54 -0400
    Re: Convert date/time to serial number and compare serial numbers GARYWC <gcotterl@earthlink.net> - 2016-11-04 10:57 -0700
      Re: Convert date/time to serial number and compare serial numbers isabelle <i@v.invalid> - 2016-11-04 14:44 -0400
        Re: Convert date/time to serial number and compare serial numbers isabelle <i@v.invalid> - 2016-11-04 15:45 -0400
    Re: Convert date/time to serial number and compare serial numbers GARYWC <gcotterl@earthlink.net> - 2016-11-04 12:15 -0700
    Re: Convert date/time to serial number and compare serial numbers GARYWC <gcotterl@earthlink.net> - 2016-11-04 14:53 -0700

#109524 — Convert date/time to serial number and compare serial numbers

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? 

[toc] | [next] | [standalone]

#109526

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

[toc] | [prev] | [next] | [standalone]

#109527

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

[toc] | [prev] | [next] | [standalone]

#109528

"GARYWC" <gcotterl@earthlink.net> wrote in message 
news:98547fda-134a-4767-b2e8-1b9ea37e2d6e@googlegroups.com...
> 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 

[toc] | [prev] | [next] | [standalone]

#109529

The cells have a text format.

[toc] | [prev] | [next] | [standalone]

#109530

also,

With Range("B1")
.Formula = "=DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))+RIGHT(A1,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.
>

[toc] | [prev] | [next] | [standalone]

#109532

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(A1,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.
>>

[toc] | [prev] | [next] | [standalone]

#109531

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.
>

[toc] | [prev] | [next] | [standalone]

#109533

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

[toc] | [prev] | [next] | [standalone]

#109535

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

[toc] | [prev] | [next] | [standalone]

#109537

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

[toc] | [prev] | [next] | [standalone]

#109536

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

[toc] | [prev] | [next] | [standalone]

#109539

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.

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.excel.programming

csiph-web