vsbabu.org

My pickle jar - Excel and Dates

One of the things I don’t want to do is to keep entering data. I prefer entering as little data as possible and then tools derive other information from it. Excel comes with pretty good formulas and functions which can be used to calculate hours between two times, days between dates etc. These are pretty straight forward.

I prefer entering times in 24 hour format. Getting hours between two times when you enter times like that involves some math. Especially since I wanted to get the hours in decimal - like 1.5, 0.25 etc.

I’ve to prepare timesheet every other Friday. Getting the next timesheet day for a date is also slightly convoluted. Luckily, that can be had if you put a date as a reference timesheet date. ie., once you enter a date that you know is a valid timesheet date, future dates can be calculated from it.

This installment has formulas on how to do the required time related calculations for my pickle jar. The columns are explained, formulas are given and a sample Excel 97 file is available for download.

Notes about columns

• TASK_DATE: What is the date in which task was done? If I do one task over multiple days, I put in multiple records, atleast one for each day.
• START and END: This is the starting and ending times on the given date for the task. Follow 24 hour clock. eg: 1:15pm is 1315. 8:45am is 845.
• HOURS: Calculated from start and end times. 1:30pm to 3:00pm gives 1.5 hours.
• WEEKOF: Calculated from task date. This is the date of the Monday for that week. I use this for some reports.
• PAYDAY: Calculated from task date and reference date (see below). This is every other Friday when one needs to submit hours worked.
• pp_ref: This is the reference date for a pay day. ie, a date from which "every other Friday"s are calculated.
• pp_refdif: Calculation field. Can be hidden. This finds out how many days have passed between reference date and task date.
• pp_refdif2: Calculation field. Can be hidden. Finds out difference in days for a fortnight.

	A	B	C	D	E	F	G	H	I
1	TASK_DATE	START	END	HOURS	WEEKOF	PAYDAY	pd_ref	pd_refdif	pd_refdif2
2	01/01/03	1315	1420	((MOD(C2,100)-MOD(B2,100)+100*(INT(C2/100)-INT(B2/100))*0.6))/60	A2 - WEEKDAY(A2,3)	IF(I2>0,G2+H2+14-I2,A2)	01/03/03	DAYS360(G2,A2,FALSE)	MOD(H2,14)

Here is a sample screenshot of how this looks:

Download Excel 97 file (15Kb).

1. Now that is a damn fine piece of coding - been looking for hour calc for a couple of hours now and yours is the first I have found that actually had a start time and end time. Thanks :)

   Posted by: Joe on February 6, 2003 01:30 AM

2. Thank you Thank you thank you. You have no idea how difficult it is to find this! You saved me from counting on my fingers!

   Posted by: Wilfred on November 2, 2004 09:06 PM