strtotime – Converting a timestamp into date

You can use the strtotime () php function to convert a string to time. When converting easy stuff like ’05/29/2019′, or ‘2019-05-29’, things are easy and predictable:

$string ="05/29/2019";
print $string ." => ". date('Y-m-d',strtotime($string));
$string ="2029-05-29";
print $string ." => ". date('Y-m-d',strtotime($string));
$string ="29.05.2019";
print $string ." => ". date('Y-m-d',strtotime($string))";

All of above will output: 2019-05-29

Which also works fine when using strings with times:

$string ="05/29/2019 16:05:01";
print $string ." => ". date('Y-m-d H:i:s',strtotime($string));
$string ="20190529T160501";
print $string ." => ". date('Y-m-d H:i:s',strtotime($string));

All of above will output: 2019-05-29 16:05:01 

When converting times you’ll need to be aware that timestamps such as ‘20190529T160501Z’ define time in UTC and their conversion will happen according to the default_timezone of your php. So…

$string ="20190529T160000Z";
print $string ." => ". date('Y-m-d H:i:s T',strtotime($string)); 
print "!!! Time conversion based on the current php time zone: ".date_default_timezone_get()." !!!";

This will output time according to the current timezone. My output would be:

2019-05-29 17:00:00 BST !!! Time zone conversion based on the current time zone php setting: Europe/London !!!

If you need to convert time in a specific timezone you need to set the default_timezone first…

$string ="20190529T160000Z";
date_default_timezone_set('America/Chicago');
print $string ." => ". date('Y-m-d H:i:s T',strtotime($string));
print "!!! Time conversion based on the current php time zone: ".date_default_timezone_get()." !!!";

Which outputs now:

2019-05-29 11:00:00 CDT !!! Time zone conversion based on the current time zone php setting: America/Chicago !!!

To convert any time to a specific time zone, need to use the “UTC” parameter:

$string ="05/29/2019 16:05:01";
date_default_timezone_set('America/Chicago');
print $string ." => ". date('Y-m-d H:i:s T',strtotime($string." UTC"))

Remember: Once you’ve used strtotime for a UTC timestamp you don’t need to do yet another conversion. Just make sure you have the correct timezone set before calling strtotime

How to open a csv file in EXCEL

If using my free ICS to CSV converter service, which reads one or multiple ics calendar file(s), converts them in comma separated files and offers such CSV files for download, you might also want to know how to open the downloaded csv file in EXCEL.

This is how to proceed…

Step 1. You start EXCEL and call ‘File > Import…

Step 2. EXCEL will prompt the text import wizard. Select there ‘CSV file’:

Step 3. Locate your CSV file and click on ‘Get Data’:

Step 4. Define the delimiter and the text qualifier. Per default my free ICS to CSV converter uses ‘tab’ as delimiter and double quotes as text qualifier.

Step 5. Check the ‘Data preview’ as offered in the ‘Text Import Wizard’. Verify that the data comes in separate columns as expected.

Step 6. Select a sheet and a cell for EXCEL to put the imported data:

Congrats. Your csv file is now imported in EXCEL :-)

UPDATE: Jan 10th, 2020

I checked today with the latest available Microsoft EXCEL (version 16.32) and noticed that importing a CSV file does not bring anymore a wizard for you to select your UTF-8 encoding or the delimiters, etc.

Important note for files containing Hebrew, Greek, Cyrillic, Korean or other special characters…

  • Rename the file ending from ‘csv’ to ‘txt’ before importing into EXCEL

  • in Microsoft EXCEL start a new blank file
  • call ‘File > Import’
  • when the import wizard prompts, select ‘Text file’
  • and locate the converted file (now ending in ‘txt’)
  • Select the correct encoding ‘Unicode (UTF-8)’

  • make sure it is set to ‘Delimited’
  • and select the correct delimiter

Merry Christmas