Timesheet formula

[kddd]

I'm wondering if anyone can give me a hand with writing a formula to
calculate hours on a timesheet? I need the formula to calculate the
number of hours of ordinary time, where ordinary time is paid for the
first 4 hours of work or until 12 noon (ie if I have worked 11am - 1 pm
there would be only 1 hour of ordinary time, 7am-3pm is 4 hours etc). I
am not using 24 hour clock or am/pm but hoping that the formula will
recognise whether the number written is in the morning or afternoon
depending on whether start time is bigger than finish time.

 

[Biff]

Hi!

I am not using 24 hour clock or am/pm but hoping that the
formula will recognise whether the number written is in the
morning or afternoon depending on whether start time is
bigger than finish time.

Bad idea!

A2 = Start = 11:00 AM
B2 = End = 1:00 PM

=IF(COUNT(A2:B2)<2,"",MIN(4/24,0.5-A2))

Format the cell as [h]:mm for a result of 1:00

Biff

 

[Arvi Laanemets]

Hi

My advice is to use conventional time formats (do you use 24-hours format or
AM/PM format is not essential at all, so long the format is a valid time
format)

When start time is in A1, and end time in A2, then time interval is
=A2-A1+(A2<A1)
and format the result in some valid time format on your choice.


Arvi Laanemets

 

[Guest]

Assuming that you do use the "hh:mm".
12:00=0.5 and 4:00 = 0.166667
Start Time is in A1 and Finish Time is in B1

=IF(B1>0.5,IF(0.5-A1>0.166667,0.166667,0.5-A1),IF(B1-A1>0.166667,0.166667,B1-A1))

That is a simple way... If your looking at Timesheets though, I'd probably
want to list all the Criteria and write a Custom Formula to include all your
Hourly Rates, Maximum & Minimum Shift Lengths, Meal Breaks etc.

Gnomie