Need help with Time


J

Jenn

Hello fellow Excel users. I've researched this group and view PERSONS
site on time...but I'm not able to come up with the correct formula.
I'm trying to create a project timesheet our tech use in the field.
However I can't get the standard time and the prime time formulas to
work correctly.

My HEADERS
A1 = Name - Manually Entered by User
B1 = Travel Start Time - Manually Entered by User
C1 = Travel Stop Time - Manually Entered by User
D1 = Project Start Time =C1 - Locked Cell-Automatically is Travel STOP
TIME.
E1 = Project Stop Time - Manually Entered by User
F1 = Total Project Hours =F1-B1 (Total travel time and project time.)
Locked Cell
G1 = Standard Hours (7:00 AM - 4:30 PM)
=IF(E1>=D1,MAX(0,MIN(E1,"16:30")-MAX(D1,"7:00")),MAX(0,"16:30"-MAX(D1,"7:00"))+MAX(0,MIN(E1,"16:30")-"7:00"))
H1 = Prime Time Hours (Anything past 4:30 PM.) =(E1<D1)+E1-D1 - G1

My problem is...I need to be able to total the Standard Hours at the
bottom when there is many days on one project. If there is no time
entered - the result in "G - Standard hours defaults to 9.30 hours."
How can I make it...."if there is time entered in this cell, then enter
time if not,,then enter 0. I am horrible with nested functions.

Same holds true for the H column. If there is no time entered...the
cell contains the #########. I need to be able to figure out the Prime
Time Hours, if any for billing purposes.

Right now I have 12 rows for a tech to enter time...but they ALL aren't
always needed for a project. Is there a way they can select how many
days and how many techs there are - and will automatically (macro
related) create the rows?

Any help? Advice one can give me? Is anyone willing to share a
workbook with me they've created? I can certainly let you see what I
have too to help me.

Purpose of this workbook - our billing is a nightmare when it comes to
this project we have.
Help! Thank you kindly in advance!
 
Ad

Advertisements

R

Roger Govier

Hi Jenn

Wrap each formula inside another IF statement

=IF(COUNT(D1:E1)<2,"", your_formula )
So if you don't have both a start and end time, it will return Null. You
can make it return 0 if you prefer.
 
Ad

Advertisements

J

Jenn

Thanks Roger...it worked. I would have never thought of that. Isn't
it funny someone can come up with something quickly-after you've racked
your brain about it.

Have a great day!
Jenni
 

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