Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Time difference calculation


Hi,

I am trying to make time sheet that would forecast employment cost.
Payment criteria is that overtime applies after midnight.
Sheet that I've designed has four columns per day IE. start & finish
time for AM shift IE. (08:00 - 16:00) and start and finish for PM
shift. That could be (17:00 - 23:00), which doesn't create problem so
far but when inputting something like (18:00 - 04:00) i get total hours
worked. What sort of formula should I use that would separate basic
hours from overtime hours automatically IE. (6hrs basic and 4hrs
overtime).

Any help on this matter would be greatly appreciated :)


--
Sasha
------------------------------------------------------------------------
Sasha's Profile: http://www.excelforum.com/member.php...o&userid=36608
View this thread: http://www.excelforum.com/showthread...hreadid=563527

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default Time difference calculation

Hi!

A1 = start time = 18:00
B1 = end time = 4:00

For Reg hrs:

=IF(COUNT(A1:B1)<2,"",IF(B1<A1,(1-A1)*24,(B1-A1)*24))

For OT hrs:

=IF(COUNT(A1:B1)<2,"",IF(B1<A1,B1*24,0))

Biff

"Sasha" wrote in
message ...

Hi,

I am trying to make time sheet that would forecast employment cost.
Payment criteria is that overtime applies after midnight.
Sheet that I've designed has four columns per day IE. start & finish
time for AM shift IE. (08:00 - 16:00) and start and finish for PM
shift. That could be (17:00 - 23:00), which doesn't create problem so
far but when inputting something like (18:00 - 04:00) i get total hours
worked. What sort of formula should I use that would separate basic
hours from overtime hours automatically IE. (6hrs basic and 4hrs
overtime).

Any help on this matter would be greatly appreciated :)


--
Sasha
------------------------------------------------------------------------
Sasha's Profile:
http://www.excelforum.com/member.php...o&userid=36608
View this thread: http://www.excelforum.com/showthread...hreadid=563527



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 510
Default Time difference calculation

Hi

The general formula for time difference calculating with midnight rollovet
taken into account
=B1-A1+(B1<A1)
where A1 contains start time, and B1 contains end time.

When allways basic time is up to 6 hours, and all over 6 hours is overtime,
then
Basic time
=MIN(6/24,B1-A1+(B1<A1))
Overtime
=MAX(0,B1-A1+(B1<A1)-6/24)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"Sasha" wrote in
message ...

Hi,

I am trying to make time sheet that would forecast employment cost.
Payment criteria is that overtime applies after midnight.
Sheet that I've designed has four columns per day IE. start & finish
time for AM shift IE. (08:00 - 16:00) and start and finish for PM
shift. That could be (17:00 - 23:00), which doesn't create problem so
far but when inputting something like (18:00 - 04:00) i get total hours
worked. What sort of formula should I use that would separate basic
hours from overtime hours automatically IE. (6hrs basic and 4hrs
overtime).

Any help on this matter would be greatly appreciated :)


--
Sasha
------------------------------------------------------------------------
Sasha's Profile:
http://www.excelforum.com/member.php...o&userid=36608
View this thread: http://www.excelforum.com/showthread...hreadid=563527



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Time difference calculation


Thanks Biff,

Formulas work very well, thanks
the only time problem occurs is when shift starts at midnight
IE (00:00 - 05:00) formula calculates all the time as basic when it
should be overtime (any hours done after midnight).

Any ideas?

Thanks,
Sasha


--
Sasha
------------------------------------------------------------------------
Sasha's Profile: http://www.excelforum.com/member.php...o&userid=36608
View this thread: http://www.excelforum.com/showthread...hreadid=563527

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default Time difference calculation

"Sasha" wrote....
Thanks Biff,
Formulas work very well, thanks
the only time problem occurs is when shift starts at midnight
IE (00:00 - 05:00) formula calculates all the time as basic when it
should be overtime (any hours done after midnight).


Well, you didn't mention that shifts can start after midnight in your
original post!

If Day shift starts at 8:00 AM does that mean any shift start from 12:00 AM
to 7:59 AM is overtime?

Biff




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Time difference calculation


Hi guys,

Thanks to Arvi problem is solved now :)

Formula for basic time is:
=IF(B1<A1,1,MAX(8/24,B1))*24-MAX(8/24,A1)*24

And formula for overtime is:
=((B1<A1)*B1 + AND(B1A1,A1<8/24)*(MIN(8/24,B1)-A1))*24

Workes well so far !

Thanks to all who tried to help :-)

Regards,
Sasha


--
Sasha
------------------------------------------------------------------------
Sasha's Profile: http://www.excelforum.com/member.php...o&userid=36608
View this thread: http://www.excelforum.com/showthread...hreadid=563527

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Averaging Time Difference vldavis809 Excel Discussion (Misc queries) 1 July 8th 06 09:42 AM
time sheet drop down lists Steve Excel Discussion (Misc queries) 12 March 18th 06 11:30 PM
Time Calculation For A Timesheet To Include Lunch poddys Excel Worksheet Functions 2 March 3rd 06 09:05 PM
perform calculation only if current time is after 10 am julieskennels Excel Worksheet Functions 7 July 18th 05 08:07 PM
Time zone Time difference mac_see Excel Worksheet Functions 0 May 3rd 05 07:57 PM


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

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

About Us

"It's about Microsoft Excel"