Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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

Image RemovedImage Added


Request Form Design

Image RemovedImage Added


Custom HTML

The custom HTML is where the effort is calculated.

Code Block
titleJavascript
<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.

Image RemovedImage Added


Download the sample app below: