Related Entries

Quick script to maintain a diary
10 annoying office phrases
Excellent article on outsourcing
Language skills for programmers

« My pickle jar - Introduction
» Musings on TEAMWORK

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

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:
sample_timesheet_01 - functions

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