Calculating Time

M

Mike Archer

Hello. I want to calculate the time a production unit is in a position. The
operator should only have to enter "time in" and "time out". The formula
should automatically deduct non-work hours. For example from 6:00 to 8:00
would be 2 hours (very simple). But from 11:00 to 14:00 would be 2 hours
also because there is a lunch from 12:00 to 13:00. From 16:00 to 7:00 should
be 2 hours also because shift ends at 17:00 and begins again at 6:00. I
can't find an example to use for this. Please help if you can!
 
S

Sandy Mann

Mike,

With the "Time In" in Cell D3 and the "Time Out" in E3 does:

=IF(COUNT(D3:E3)<>2,"",IF(AND(D3<0.5,E3>0.5),E3-D3-1/24,IF(E3<D3,(17/24-D3)+(E3-6/24),E3-D3)))

do what you want?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
M

Mike Archer

Sandy - That is very helpful. I made a mistake though. I thought that if
someone provided me a formula, I would be able to figure out how it works,
therefore, I gave hypothetical times to keep the example simple. I am not
able to decypher your formula. Would you mind helping modify it for the real
times?
The shift is 6:00 to 16:30, and lunch is 11:00 to 11:30.
--
Thanks,
Mike


Sandy Mann said:
Mike,

With the "Time In" in Cell D3 and the "Time Out" in E3 does:

=IF(COUNT(D3:E3)<>2,"",IF(AND(D3<0.5,E3>0.5),E3-D3-1/24,IF(E3<D3,(17/24-D3)+(E3-6/24),E3-D3)))

do what you want?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
S

Sandy Mann

Try:

=IF(COUNT(D3:E3)<>2,"",IF(AND(D3<=11/24,E3>=11.5/24),E3-D3-0.5/24,IF(E3<D3,(16.5/24-D3)+(E3-6/24),E3-D3)))

Times as fractions of a day so 1 hour is 1/24th of a day ie 1/24 so 11:30 AM
is 11.5/24 etc

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Mike Archer said:
Sandy - That is very helpful. I made a mistake though. I thought that if
someone provided me a formula, I would be able to figure out how it works,
therefore, I gave hypothetical times to keep the example simple. I am not
able to decypher your formula. Would you mind helping modify it for the
real
times?
The shift is 6:00 to 16:30, and lunch is 11:00 to 11:30.
 
M

Mike Archer

Thank you so much!
--
Thanks,
Mike


Sandy Mann said:
Try:

=IF(COUNT(D3:E3)<>2,"",IF(AND(D3<=11/24,E3>=11.5/24),E3-D3-0.5/24,IF(E3<D3,(16.5/24-D3)+(E3-6/24),E3-D3)))

Times as fractions of a day so 1 hour is 1/24th of a day ie 1/24 so 11:30 AM
is 11.5/24 etc

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
S

Sandy Mann

You're very welcome. My wife's got me scraping wallpaper so it was a
welcome breal <g>

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top