Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 80
Default 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?
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 538
Default Convert date/time to serial number and compare serial numbers

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
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 80
Default Convert date/time to serial number and compare serial numbers

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
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 162
Default Convert date/time to serial number and compare serial numbers


"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


  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 80
Default Convert date/time to serial number and compare serial numbers

The cells have a text format.


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 99
Default Convert date/time to serial number and compare serial numbers

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.

  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 99
Default Convert date/time to serial number and compare serial numbers

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.

  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 99
Default Convert date/time to serial number and compare serial numbers

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.

  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 80
Default Convert date/time to serial number and compare serial numbers

How can I convert serial number 42361.53146 to a date and time?
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 99
Default Convert date/time to serial number and compare serial numbers


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


  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 80
Default Convert date/time to serial number and compare serial numbers

What is the formula when the serial number is in R2?
  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 99
Default Convert date/time to serial number and compare serial numbers

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

  #13   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 80
Default Convert date/time to serial number and compare serial numbers

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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert to serial numbers and compare serial numbers GARYWC Excel Programming 0 November 3rd 16 10:32 PM
Convert dates and times into serial number and compare serial numbers GARYWC Excel Programming 0 November 3rd 16 08:02 PM
Convert date to serial number callbr549 Excel Programming 12 March 30th 09 10:35 PM
convert serial number into date santhu Excel Discussion (Misc queries) 2 October 28th 06 10:27 AM
Convert date to serial number in VBA Michael J. Malinsky Excel Programming 1 September 10th 03 08:43 PM


All times are GMT +1. The time now is 09:40 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"