Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have lots of dates in the first column in the format

August 23, 2010 at 11:01PM

I was wondering if anyone knows 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.

It's the format IFTTT outputs

share|improve this question

migrated from superuser.com Sep 15 '13 at 12:10

This question came from our site for computer enthusiasts and power users.

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 Sep 11 '13 at 18:33
up vote 8 down vote accepted

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

share|improve this answer
The TIMEVALUE formula only exists in the new Google Sheets. – Jacob Jan Tuinstra Jul 24 '15 at 14:18
@JacobJanTuinstra do you know if that is also the case for DATEVALUE ? – jamesmstone Jul 27 '15 at 7:28
@jamesmstone the DATEVALUE formula exists in both versions. – Jacob Jan Tuinstra Jul 27 '15 at 8:01
+1 @Nuno Cruces Updating to use =Value – jamesmstone Feb 15 at 4:40

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

share|improve this answer
I believe corr should equal 12however as the edit would be less then 6 characters i cant change it – jamesmstone Sep 15 '13 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? – jamesmstone Sep 15 '13 at 22:33
I have updated @Jacob 's example sheet to reflect this – jamesmstone Sep 15 '13 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 Tuinstra Sep 16 '13 at 12:24
Thanks. Quick question is the function parseInt() the same as the javascript parseInt() . do google macros use javascript? – jamesmstone Sep 16 '13 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

share|improve this answer
Why would one visit Microsoft Developer Network for formats in Google Spreadsheets? – Al E. May 9 '15 at 13:39

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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