A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Timesheet Formula Problem



 
 
Thread Tools Display Modes
  #1  
Old June 11th, 2004, 10:30 PM
Bridie
external usenet poster
 
Posts: n/a
Default Timesheet Formula Problem

I have a number of timesheets for various departments up and
running correctly. However, I am trying to change one to calculate
hours over a 24 hour period where staff are on either a day rate or a
night rate depending on the time they start and finish. To complicate
things further some staff's hours are spread over both times i.e. from
the 7 in the morning to 7 in the evening is at day rate and from 7 in
the evening through to 7 in the morning is at night rate. Start time is
in column R5 and finish time is in column S5 (24 hour clock format). I
can calculate the hours between both but I cannot get a working formula
to break this up into day and night hours. After the start time and the
finish time I started using two colums to give me day hours and night
hours and I thought I had it sorted until I came to a person who
started at 1pm (13.00) but finished at 9pm (21.00). I would be grateful
for any assistance as I can't seem to move any further with this.
IF(AND(R5DayStartDayEnd,S5=DayEnd),S5-R5,"") formula for day hours
in column T5
IF(AND(R5=DayEnd,S5=DayStart),24-R5+S5,"") formula for night hours in
column U5
Any help would be much appreciated.
The above are giving me the correct figures on someone starting at 7 pm
and finishing at 7am. How do I get to work where someone starts at say
7am and finishes at 8pm.


---
Message posted from http://www.ExcelForum.com/

  #2  
Old June 11th, 2004, 11:41 PM
Frank Kabel
external usenet poster
 
Posts: n/a
Default Timesheet Formula Problem

Hi
for getting the night hours try:
=24*IF(B1A1,MAX(B1-MAX(A1,19/24),0),1-MAX(A1,19/24)+MIN(B1,7/24))

day hours:
=24*IF(B1A1,MAX(MIN(B1,19/24)-MAX(A1,7/24),0),MAX(19/24-MAX(A1,7/24),0
)+MAX(B1-7/24,0))


A1: starting time
B1: finishing time

--
Regards
Frank Kabel
Frankfurt, Germany


confused: I have a number of timesheets for various departments up
and

running correctly. However, I am trying to change one to calculate
hours over a 24 hour period where staff are on either a day rate or a
night rate depending on the time they start and finish. To complicate
things further some staff's hours are spread over both times i.e.

from
the 7 in the morning to 7 in the evening is at day rate and from 7 in
the evening through to 7 in the morning is at night rate. Start time
is in column R5 and finish time is in column S5 (24 hour clock
format). I can calculate the hours between both but I cannot get a
working formula to break this up into day and night hours. After the
start time and the finish time I started using two colums to give me
day hours and night hours and I thought I had it sorted until I came
to a person who started at 1pm (13.00) but finished at 9pm (21.00). I
would be grateful for any assistance as I can't seem to move any
further with this. IF(AND(R5DayStartDayEnd,S5=DayEnd),S5-R5,"")
formula for day hours in column T5
IF(AND(R5=DayEnd,S5=DayStart),24-R5+S5,"") formula for night hours
in column U5
Any help would be much appreciated.
The above are giving me the correct figures on someone starting at 7
pm and finishing at 7am. How do I get to work where someone starts at
say 7am and finishes at 8pm.


---
Message posted from http://www.ExcelForum.com/


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:12 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.