I have lots of dates in the first column in the format output by IFTTT:

August 23, 2010 at 11:01PM

Is there a way to get them formatted in a way that I can then use for date and time calculations?

Ideally the function will only take up an extra column.

  • I don't believe there is a built in function that will format the date in the way that you want it to. Google Drive has a built-in Script Editor (Tools - Script Editor) you can use to write a custom script to do what you want. You could post a question on StackOverflow if you get stuck.
    – yassarikhan786
    Commented Sep 11, 2013 at 18:33

3 Answers 3


I use this formula to convert the date time values IFTTT spits out to a useful format:

=VALUE(SUBSTITUTE(A1, " at ", " "))

Where A1 is the cell containing the date string

This gives you a decimal number that you can format as "Date Time".

  • 2
    The TIMEVALUE formula only exists in the new Google Sheets.
    – Jacob Jan
    Commented Jul 24, 2015 at 14:18
  • @JacobJanTuinstra do you know if that is also the case for DATEVALUE ? Commented Jul 27, 2015 at 7:28
  • 2
    @jamesmstone the DATEVALUE formula exists in both versions.
    – Jacob Jan
    Commented Jul 27, 2015 at 8:01

The following custom function will transform the string date notation into a date notation:


function myDate(range) {
  var output = [];  
  for(var i=0, iLen=range.length; i<iLen; i++) {
    var rSplit = range[i][0].split(" "), corr = 0;
    if(rSplit[4].substring(5,7) == "PM") {
      corr = 10;
    var month = parseInt(new Date(Date.parse(rSplit[0]+"1,2013")).getMonth());
    var day = parseInt(rSplit[1].replace(/,/g, ""));
    var year = parseInt(rSplit[2]);
    var hour = parseInt(rSplit[4].substring(0,2))+corr;
    var min = parseInt(rSplit[4].substring(3,5),10);    
    var nDate = new Date(year, month, day, hour, min);
  return output;


Correction is made to account for AM/PM. No correction for GMT. Add this script via the Tools menu in the spreadsheet and select Script editor. Save the script and you are ready to go !!




I've created an example file for you: string date to date

  • I believe corr should equal 12however as the edit would be less then 6 characters i cant change it Commented Sep 15, 2013 at 22:08
  • After testing the code. I noticed for the time 11:09PM (regardless of the year) it always returns 1/1/1970 11:00:00. So for example August 23, 2010 at 11:09PM returns 1/1/1970 11:00:00 do you have any idea why? Commented Sep 15, 2013 at 22:33
  • 1
    I have updated @Jacob 's example sheet to reflect this Commented Sep 15, 2013 at 22:40
  • @jamesmstone see revised code, it works for the example in the spreadsheet.I had to force the parseInt(text, 10) to decimals. The corr is set at corr=10 because this will yield the same time. I did not take into account the different time zones.
    – Jacob Jan
    Commented Sep 16, 2013 at 12:24
  • Thanks. Quick question is the function parseInt() the same as the javascript parseInt() . do google macros use javascript? Commented Sep 16, 2013 at 22:05

You can also use one of the display format scripts, this keeps the underlying numbers the same as normal display formats and as such won't corrupt any date/time numerical calculations.

so a script search for 'Format' and get one of the number and date ones, the format to be used is the same as the =Text(,"") variation without needing to be in sheet converted.

MMMM d, yyyy at h:mma


August 23, 2010 at 11:01PM

For other formatting options see: http://msdn.microsoft.com/en-us/library/8kb3ddd4(v=vs.110).aspx

  • 3
    Why would one visit Microsoft Developer Network for formats in Google Spreadsheets?
    – ale
    Commented May 9, 2015 at 13:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.