Timesheet - Start Time add 8.5hrs

[DJChristo]

Hi all. long time reader first time poster.

I am trying to automate the calculation of my time sheet using Excel.
We currently use excel and it is very labour intensive, inserting all cell data (dates, times, etc) manually.

My design details are:
Start time varies from day to day
Time is entered in 24hrs (eg: 8am start is 800) (the 0 is omitted, not necessary)
30 mins for lunch each day
8hrs work each day
Finish time = Start time + 30mins lunch + 8hrs work

eg: Start 0800, + 30mins lunch + 8hrs work = 1630 finish time
I have this formula worked out which works well for round numbers (eg full hours, or half hour starts)
Add 830 to start time, but it only works for 0800, 0815, 0830 start times. as soon as i enter 0845 it goes crazy!

eg: 0745 (7.45am), the result would be 0745+30+800=1675 which is not a real time, the real answer should be 1715 (5.15pm)

I have tried calculating it using "time" instead of a number, but it gets messy involving fractions of numbers and i would rather avoid that.

I think I need an IF formula, IF answer ends in 75 then, add 45 to overall number. Will that work?

Can someone please help to construct an IF statement for me.

 

[DJChristo]

Just had to alter a few of my math errors below:

Hi all. long time reader first time poster.

Add 830 to start time, but it only works for 0800, 0815, edit 0830 and 0845 it goes crazy!

eg: 0745 (7.45am), the result would be 0745+30+800=1675 which is not a real time, the real answer should be 1715 (5.15pm)
I think I need an IF formula,
IF answer ends in 75 then, add 40 to overall number. 1675 becomes 1715 (5.15pm)
AND IF answer ends in 60 then, add 40 to overall number. 1660 becomes 1700 (5pm)

 

[mole999]

Nightmare to not use proper dates and times, excel handles them very well, your using text so its not being treated as numeric

if of formated the cells as time then add 0.35 to it your 08:00 will become 16:30 ( a day = 1 as a whole number so time is a fraction of 1 (12 hours is 0.5 days)

 

[DJChristo]

Thanks Mole999 I will try to rejig it using Time formulas. I had a go once but it scared me.

 

[mole999]

its far easier than trying to account for every hour. MS even provide templates Weekly time sheet with breaks - Templates

 

[Snakehips]

DJChristo,

I wholeheartedly agree with Mole re using proper dates and or times. The worst thing you may have to do is format your cells as hh:mm and be prepared to enter the time with hour : min.

I would however question 08:00 + 0.35 as resulting in 16:30 ?? I would make that 16:24 ! 08:00 + 0.354167 = 16:30

Maybe, rather than struggle with envisaging hours and minutes as a decimal fraction of 1 day i.e. 8hrs 30min = 0.354167 you express it as decimal hours / 24?

ie 8 hrs 30 min = 8.5/24

See below.....

Excel 2007
	A	B	C
4		Snake	Mole
5	Start time	08:00	08:00
6	Finish time	16:30	16:24
Sheet1

Cell Formulas
Range	Formula
B6	=B5+(8.5/24)
C6	=B5+0.35

Hope that helps.

 

[mole999]

DJChristo,


I would however question 08:00 + 0.35 as resulting in 16:30 ?? I would make that 16:24 ! 08:00 + 0.354167 = 16:30

Thats what happens when you stuff 08:30 into a cell and then return it as a number in another to two decimal places

 

[Snakehips]

Thats what happens when you stuff 08:30 into a cell and then return it as a number in another to two decimal places

You're by no means the first to do something that nor will you be the last. This idiot, for one, will see to that!!