Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 22nd 19, 09:20 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 144
Default Converting a date number?

I have some video files captured automatically and given file names
which must be date/times. Here are a couple of examples:

1550820358.MOV was captured at about 07:25 on 22nd Feb 2019.
1550823001.MOV was captured at about 08:10 on the same morning (today).

So they must be in seconds from some base date. To avoid reinventing
this particular wheel, could someone kindly share a formula and/or VBA
macro that would convert those numbers to a date and time in my
favourite format please.

IOW, convert 1550820358 to 20190222-072558, i.e. just before 7:26 am on
22nd February 2019.

Terry, East Grinstead, UK

  #2   Report Post  
Old February 22nd 19, 10:00 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,669
Default Converting a date number?

Hi Terry,

Am Fri, 22 Feb 2019 09:20:30 +0000 schrieb Terry Pinnell:

I have some video files captured automatically and given file names
which must be date/times. Here are a couple of examples:

1550820358.MOV was captured at about 07:25 on 22nd Feb 2019.
1550823001.MOV was captured at about 08:10 on the same morning (today).

So they must be in seconds from some base date. To avoid reinventing
this particular wheel, could someone kindly share a formula and/or VBA
macro that would convert those numbers to a date and time in my
favourite format please.

IOW, convert 1550820358 to 20190222-072558, i.e. just before 7:26 am on
22nd February 2019.


1550820358.MOV in A1. Then:
=DATE(1970,1,1)+LEFT(A1,LEN(A1)-4)/86400


Regards
Claus B.
--
Windows10
Office 2016
  #3   Report Post  
Old February 22nd 19, 02:03 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 144
Default Converting a date number?

Claus Busch wrote:

=DATE(1970,1,1)+LEFT(A1,LEN(A1)-4)/86400


Excellent, thanks Claus!

Best wishes,

Terry, East Grinstead, UK


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
Converting Number to Date billbrandi Excel Discussion (Misc queries) 2 December 28th 07 09:25 PM
Converting a number to a date Pete Excel Discussion (Misc queries) 3 February 6th 07 03:51 AM
converting date to number Todd Excel Worksheet Functions 9 September 3rd 06 01:33 AM
converting 8-digit number to date DJ Excel Discussion (Misc queries) 6 June 12th 06 11:44 PM
converting a number to a date solo_razor[_26_] Excel Programming 2 November 4th 03 02:40 PM


All times are GMT +1. The time now is 12:09 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017