help with if statement 1

[JesseNorris]

hi all

i have made a timesheet for my work place
but i have hit a brick wall.
we have an 8 hour day but only get paid for 7
the "lunch hour" can be taken any time we like. that was the easy bit.

now if we have a lunch brake that is less than 1 hour we can claim toil for the time between when we come back and the end of that hour. this info needs to be in a difrent col called toil. the boss wants the spreedsheet to show the times that we take e.g if the lunch hour was 12 - 1 but i only took 12 - 12:30 he wants the spreedsheet to put in the toil col 12:30 - 1:00 and add .5 to the hours col.
is this possible?

if it is can you help me out with a statement or code that will do this.

thanks all
Regards,
Jesse

 

[KenWright]

So how is your spreadsheet laid out? Do you have a cell that contains the start time and finish time of your lunch period. If so then how about:-

Assuming your lunch start time was in cell A1 and finish was in A2 then

=(TIME(1,0,0)-(A2-A1))*24

It could also just be written as

=1-(A2-A1)*24

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[JesseNorris]

thanks ken i will give it a go

regards,
Jesse

 

[JesseNorris]

hi ken
taht works good for working out the amount of time for over time do you have any ideas for the other values?
regards,
Jesse

 

[KenWright]

Chip Pearson has a whole host of formulas for dealing with Dates and times here:-

http://cpearson.com/excel/datetime.htm

Regards
Ken................



----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[JesseNorris]

thanks ken

 

[KenWright]

You're welcome

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------