## My pickle jar - Excel and Dates

Getting information from date columns in Excel

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: 