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!
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!