Convert times to decimal | Google scripts | Spreadsheets | Toggl

If in a Google Docs spreadsheet you want to convert a row of values like

"1:36:54"

which represents (hours:minutes:seconds) to something like this

"1.615"

then you are in the right place.

This is a script that will allow you to batch proccess times imported from a program like Toggl (toggl.com) to a decimal form of hours from a string like hours:minutes:seconds. Just create a script in your spreadsheet by clicking tools "script manager" - > "new…"

Then cut and past the following code in, save and then play with it on some test data before applying it to your actual spreadsheet.

	function hours_to_dec() {

	//these are all the variables we are using with an indicator of each's type.

	// reference to active spreadsheet object

	var ss = {};

	// which sheet in the spreadsheet object will we be using

	var sheetNum = 0;

	// reference to the sheet in the spreadsheet

	var sheet = {};

	// incrementer for when we parse through each field

	var i = 1;

	// the row number to stop at

	var limit = 2;

	// the string that defines the selection of the string '00:00:00' (hours:minutes:seconds)

	var range = '';

	// the string that defines the field where we will output the decimal form of the string (hrs:min:sec) that we pulled from range

	var rangeInput = '';

	// the string version of (hrs:min:sec), it needs to be a string so we can use split on it to separate hours, minutes and seconds

	var strValue = '';

	// the final decimal value of the transformed string

	var value = 0;

	// we split up the time string into 3 parts and put them into list list[0] = hours, list [1] = min etc, etc

	var list = [];

	// number of hours

	var num0 = 0.0;

	// number of minutes in a decimal form of hours, eg. half an hour or 30 min = 0.5 hrs

	var num1 = 0.0;

	// number of seconds in a decimal form of hours,

	var num2 = 0.0;

	// the string version of the decimal so that we can truncate the percision to 8 digits

	var strNum = "";

	// this is a user inputed row number where to start

	var startingRowNum = 0;

	// this is a user inputed row number where to end

	var endingRowNum = 0;

	// this is the user inputed column where the time string is coming from

	var columnIn = 'C';

	// this is the user inputed adjacent column where the decimal form will be inputed on the same row number

	var columnOut = 'D';



	//get the reference to the spreadsheet

	ss = SpreadsheetApp.getActiveSpreadsheet();



	//this calls up ui that requests input, it asks the user which sheet inside the spreadsheet to use

	// a spreadsheet can contain multiple sheets denoted by tabs at the bottom of the google doc

	// 0 pertains to the first sheet, 1 to the second and on

	sheetNum = Browser.inputBox("Enter Sheet Number (0 is sheet 1, 1 is sheet 2, and so on)");



	//check user input

	// make sure the user input is conformed to an integer.

	sheetNum = parseInt(sheetNum);

	// make sure the user entered a number

	if ((sheetNum+'').toLowerCase() == 'nan'){

	sheetNum = 0;

	}



	//get the reference to the sheet inside the spread sheet

	sheet = ss.getSheets()[sheetNum];

	//NOTE: I do not check to make sure this sheet exists so the input has to be correct



	//ask the user from what row number to proccess

	startingRowNum = Browser.inputBox("Enter Starting Row Number");

	//check input

	startingRowNum = parseInt(startingRowNum);

	if (startingRowNum < 1 \|\| (startingRowNum+'').toLowerCase() == 'nan'){

	startingRowNum = 1;

	}

	//ask the user to what row number to proccess

	endingRowNum = Browser.inputBox("Enter Ending Row Number");

	//check input

	endingRowNum = parseInt(endingRowNum);

	if (endingRowNum < startingRowNum \|\| (endingRowNum+'').toLowerCase() == 'nan'){

	endingRowNum = startingRowNum;

	}

	//set the loop limit to the last row specified

	limit = endingRowNum;



	//this is the user inputed column where the time string is coming from

	columnIn = Browser.inputBox("Enter Input Row Letter");

	//this is the user inputed adjacent column where the decimal form will be inputed on the same row number

	columnOut = Browser.inputBox("Enter Output Row Letter");

	//normalize the input

	columnIn = columnIn.toUpperCase();

	columnOut = columnOut.toUpperCase();

	//NOTE: I do not check to make sure these rows do exist, so the input has to be correct



	//now loop through everything between the starting and ending row

	for (i = startingRowNum; i <= limit; i++){

	//define the string that represents the selection like 'A1'

	range = columnIn+''+i;

	//define the string that represents the field where the program will out put the floating point version of the time string

	rangeInput = columnOut+''+i;

	//make sure that the time string is in a string format otherwise it will return weird stuff, something like "Sun, 12/30/2012, 10:20:03"...etc

	sheet.getRange(range).clearFormat();

	sheet.getRange(range).setNumberFormat('@STRING@');

	//get the value and type cast it to a string

	strValue = sheet.getRange(range).getValue()+'';

	//the string should be something like '01:23:47'

	list = strValue.split(":");

	//since alert is forbiden in google scripts I use the input box for quick debugging, google has a logging system that works better.

	//Browser.inputBox(parseInt(list[0]));

	//get the number of hours

	num0 = parseInt(list[0]);

	//get the decimal form by dividing the minutes by 60 to get decimal hours

	num1 = parseInt(list[1])/60;

	//get the decimal form of seconds by dividing the minutes by the number of seconds there is in an hour

	num2 = parseInt(list[2])/(60*60);

	//get the floating point part of the total figure

	value = num1+num2;

	//type cast it to a string

	strNum = value+"";

	//truncate the string to a precision of 8

	strNum = strNum.substr(0,8);

	//parse the string back to a floating point number and then add in the amount of hours to get the full figure

	value = num0+parseFloat(strNum);

	//set the output field to the value

	sheet.getRange(rangeInput).setValue(value);

	//set the precision of the field

	sheet.getRange(rangeInput).setNumberFormat("0.00000");

	}

	}

view raw convertHrsMinSec2HrsDec.js hosted with ❤ by GitHub

I wrote this because I wanted to use toggl to export a CSV file and then import it into a Google Doc Spreadsheet. This was easy enough but I wanted to charge my hourly rate on the time spent since it was in this time format (hours:minutes:seconds) I could not multiply my hourly rate times this number so I had to build a script to convert all the time codes to decimal representations in hours. This is a good work around if you don't want to pay toggle for the extended account that allows you to create invoices and such. I hope it helps you out.