Calculating Time (2002 SP3)

Topic

New Reply

(Edited by Kel on 02-Nov-04 09:35. )

I am trying to setup a formula for a timesheet, however I am unsure of what functions I might use to make this happen. My table looks something like this:

IN	OUT	IN	OUT	IN	OUT	HOURS
8:00	10:00	10:30	12:00	12:30	5:00	??

I’d love for this thing to calculate out the amount of time I was actually here automatically. Any ideas? Note that hours may vary widely, I can arrive as early as 4:00am, and/or leave as late as 9:00pm.

Thanks!

Reply | Quote

Replies

You will need to specify AM and PM in the time, or use 24 hour time, and, if the times will ever run from one day to the next, you’ll also need the date, but you should still format the cells as “h:mm AM/PM” or “h:mm” for 24 hour time.

Kel, if I copy what you posted into Excel, it tends to assume everything is AM, but I expect the 5:00 is PM? Once you resolve that, the general fromula for elapsed time will be

	A	B	C	D	E	F	G
1	IN	OUT	IN	OUT	IN	OUT	HOURS
2	8:00 AM	10:00 AM	10:30 AM	12:00 PM	12:30 PM	5:00 PM	=B2+D2+F2-A2-C2-E2

All formatted as “h:mm AM/PM” except G2 which is “h:mm”.

Reply | Quote

I suggest formatting G2 to “[h]:mm” to allow for elapsed times (> 24 hour) rather than just time of day.

Steve

Reply | Quote

To rephrase Peewee Herman, “I meant to say that!”

Reply | Quote

It was probably just a typo

Steve

Reply | Quote

It was probably just a typo

Steve

Reply | Quote

To rephrase Peewee Herman, “I meant to say that!”

Reply | Quote

I suggest formatting G2 to “[h]:mm” to allow for elapsed times (> 24 hour) rather than just time of day.

Steve

Reply | Quote