#1   Report Post  
Rusty Boy
 
Posts: n/a
Default time sheet

I'm trying to calculate time on a excel spreadsheet. Example if you work 5
hours or less then just calculate the hours worked. But if you work more
then 5 hours then subtract 1/2 hour for lunch. Here is the formula i'm trying
c17 is start time and d17 is end time. in this example they work 10 hours
and it should minus .5 for lunch.
Start time 6:00 AM - End time 4:00 pm, shoule = 9.5, I get -.05
=((D17-C17)*24)*IF(D17-C175,(D17-C17)*24)*IF(D17-C17<5,(D17-C17)*24)-0.5

Thank's for any help I can get.


  #2   Report Post  
Art
 
Posts: n/a
Default

Try this:

=(D17-C17)*24-IF((D17-C17)*245,0.5,0)

Art

"Rusty Boy" wrote:

I'm trying to calculate time on a excel spreadsheet. Example if you work 5
hours or less then just calculate the hours worked. But if you work more
then 5 hours then subtract 1/2 hour for lunch. Here is the formula i'm trying
c17 is start time and d17 is end time. in this example they work 10 hours
and it should minus .5 for lunch.
Start time 6:00 AM - End time 4:00 pm, shoule = 9.5, I get -.05
=((D17-C17)*24)*IF(D17-C175,(D17-C17)*24)*IF(D17-C17<5,(D17-C17)*24)-0.5

Thank's for any help I can get.


  #3   Report Post  
Bob Phillips
 
Posts: n/a
Default

An alternative

=D17-C17-((D17-C17)1/8)*1/48

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Art" wrote in message
...
Try this:

=(D17-C17)*24-IF((D17-C17)*245,0.5,0)

Art

"Rusty Boy" wrote:

I'm trying to calculate time on a excel spreadsheet. Example if you

work 5
hours or less then just calculate the hours worked. But if you work

more
then 5 hours then subtract 1/2 hour for lunch. Here is the formula i'm

trying
c17 is start time and d17 is end time. in this example they work 10

hours
and it should minus .5 for lunch.
Start time 6:00 AM - End time 4:00 pm, shoule = 9.5, I get -.05

=((D17-C17)*24)*IF(D17-C175,(D17-C17)*24)*IF(D17-C17<5,(D17-C17)*24)-0.5

Thank's for any help I can get.




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
How do I set up a formula on a time sheet to calculate time in 1/. gschmid Excel Discussion (Misc queries) 2 January 18th 05 02:48 PM
Time sheet set up specifically HT New Users to Excel 5 December 5th 04 06:56 PM
Time Sheet calculations dkostel Excel Worksheet Functions 2 November 23rd 04 11:11 PM
Naming & renaming a sheet tab Cgbilliar Excel Worksheet Functions 1 November 7th 04 06:57 PM


All times are GMT +1. The time now is 12:01 AM.

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"