This post will teach you how to calculate the time between two dates using Advanced Grid. This app will calculate the difference in hours and minutes however, you can adapt it accordingly to your needs. In this app, we will call the difference between the two dates, effort.

Form Design


Grid Form Design


Request Form Design


Custom HTML

The custom HTML is where the effort is calculated.

Javascript
<script>
	$(function() {
		var grid = FormUtil.getField("advgrid"); // Field id of advance grid

		$(grid).on("change", function() {
			var start = FormUtil.getGridCellValues("advgrid.start");
			var end = FormUtil.getGridCellValues("advgrid.end");
			var totalHrs = 0;
			var totalMins = 0;

			for (var i = 0; i < start.length; i++) {

				var startDate = new Date(start[i]);
				var endDate = new Date(end[i]);
				var diffMs = (endDate - startDate);

				var diffHrs = Math.floor(diffMs / 3600000); // Hours
				var diffMins = Math.round((diffMs % 3600000) / 60000); // Minutes
				var totalDuration = diffHrs + ":" + diffMins;
				totalHrs = totalHrs + diffHrs;
				totalMins = totalMins + diffMins;
				var total = totalHrs + ":" + totalMins;
				

				setValue(grid, i, 3, totalDuration);
				setValue(grid, i, 4, total);

			}
		});

		function setValue(grid, rowIndx, colIndx, colValue) {
			var DM = $(grid).find(".pq_grid").pqGrid("option", "dataModel");
			var data = DM.data;
			var json = data[rowIndx][data[rowIndx].length - 1];
			var obj = eval("[" + json + "]");

			var colKey = DM.columnKey[colIndx];

			obj[0][colKey] = colValue;

			//Update data
			json = JSON.encode(obj);
			json = json.substring(1, json.length - 1);
			DM.data[rowIndx][colIndx + 1] = colValue;
			DM.data[rowIndx][data[rowIndx].length - 1] = json;

			//Update hidden table
			var indexKey = DM.data[rowIndx][0];
			var tbl = $(grid).find('.table_container table');
			tbl.find("tr.key_" + indexKey).find("[column_key=" + colKey + "]").text(colValue);
			tbl.find("tr.key_" + indexKey).find("textarea").val(json);

			//Update table cell
			var colCell = $(grid).find("tr[pq-row-indx=" + rowIndx + "] .pq-grid-cell[pq-col-indx=" + (colIndx + 1) + "]");
			$(colCell).find(".pq-td-div").html('<div class="form-cell-value"><span>' + colValue + '</span></div>');
		}
	}); 
</script>

Runtime

This is the result of the above code.


Download the sample app below:

  • No labels