Conditional Formatting Using Hours/time

Hello,

Please help!!!
We have a client who will pay using the 1/4, 1/2/ and 3/4 of an hour increments as follows:

From the hour mark to 6 minutes = 0 time paid
Over 7 minutes to 15 minutes = 1/4 hour 15
16 minutes to 30 minutes = 1/2 hour 30
30 minutes to 45 minutes = 3/4 hour 45
46 minutes to 60 minutes = one hour.

They work on shifts and the operation is 24 hours. Each gets 30 minutes for breaks which is not paid. Pay is biweekly and overtime is anything over 40 hours. Currently, the template we are using looks like this (sorry, I could not figure out how to paste it so you can see it in Excel layout):
WEEK 1 Sunday Monday Tuesday Wednesday Thursday Friday Saturday
5/16 5/17 5/18 5/19 5/20 5/21 5/22
TIME IN ENTER TIME, INCLUDE AM OR PM
TIME OUT
TIME IN
TIME OUT
TIME IN
TIME OUT
TIME IN Total Hours First Week
TIME OUT
HOURS WORKED-OR- choose one
*HOLIDAY WORKED
PAID HOLIDAY (not worked)
*OTHER PAID HOURS
EXPLANATION OF *HOLIDAY WORKED HRS OR *OTHER

I am very new to Excel and need help creating a formula that will allow us to calculate this timesheet? I am in desperate need for help.

Thanks..

I am looking for assistance in having one cell in a text format equals another cell that contains a time value in hh:mm format.

For example: Cell A1 has a time format (hh:mm) value of 04:00; which is the Start Time. I would like cell D1 to have a text format value of "04:00" (result is dependant upon what is entered in A1). I would duplicate the same formulas to reflect Stop Times in other cells.

My final result is to have another cell (F1) use the Concatenate formula to have the Start and Stop time shown in one cell as "04:00 - 12:30". The times would change based on the Time formated values entered into the Start and Stop time cells.

I have researched this in the board and found many excellent ways to do the opposite, but not convert Time format to Text format. Any assistance is greatly appreciated.

-Shane

Entering time values in custom format [h]:mm:ss
Cells accept hours over 23,
Adding cells in column returns correct total time.
Have not found a way to multiply these cells by a $ hourly rate.
So use HOUR(cell ref) and MINUTE(cell ref) to capture values in referenced
cell - then use these values to calculate total payment for $rate per hour.
However, the HOUR(cell ref) formula returns the hours in excess of 24 when
the cell contains an hour value in excess of 23 (ie 27 hours returns 3).

I am trying to calculate time based off a non-conventional quarter hour time system

example;
8:00 to 8:07 = 8.0 hrs
8:08 to 8:23 = 8.25 hrs
8:24 to 8:38 = 8.5 hrs
8:39 to 8:53 = 8.75 hrs
8:53 to 8:59 = 9.0 hrs

I am having trouble writing an equation that would sum the clock-in and clock-out times (2 per day) and display the total time in these quarter hours and adding an additional hour if it is >= to *:53. Any help would be great.

Hi
I am using Excel 2007 and suddenly I can no longer filter by color. The option is greyed out and so is the sort by Color option. Does anyone have any idea why this would happen?

Thanks in advance.

Hi all,

I have a dillemma here,

I have to create a schedule which incorporates lunches automatically (either half hour or 1-hour lunches, depending on the circumstances).

The schedule only has time in and time out, but I need excel to automatically deduct the lunch break depending on the circumstance:

If you worked less than 6 hours = no lunch
If you worked more than 6 hours, but less than or equal to 8 hours = 0.5 hour lunch
If you worked more than 8 hours = 1 hour lunch

I am assuming the formula for this will be really long, but I have looked online everywhere and have not found ONE formula for it. I can't put lunch breaks seperately, so all I have to work with is Time in/out.

Also, I wouldn't be able to use military time, so I don't know how excel can assume that time in is in the morning, and time out is in teh evening and/or half-day.

If someone can help it would be greatlyyyy appreciated

I have cells in a column, some colored yellow, some not. I am trying to use
the SUMIF function to sum only the cells in the range that are colored. Can
this be done? Can I enter something in the "criteria" part of the formula
that can do this?

=SUMIF(D3:D13,"criteria",D3:D13)

I also tried to use the CELL function's color feature, but I couldn't get it
to work right. I don't know how to get Excel to recognize if a cell is
colored in a formula.
=CELL("color",cell)

It might just be that I don't know what this means in Help:

"color" --> 1 if the cell is formatted in color for negative values;
otherwise returns 0 (zero).

Can anyone help?

I am trying to calculate the average Talk Time into Minutes and Seconds. Every time I have tried, I get the wrong average.

For example, if I have a total of 53 calls and the total talk time is 4:19:05, what would the average talk per call be? I tried doing everything in seconds (this would give me a total of 15545 seconds) but the total I was getting is 4:89.

What would the formula be to get the correct average time?

I am a very advanced Excel user so this is a new one for me.

When I open any spreadsheet (new or already populated) the black box that normally frames the active cell is missing (showing which cell you have selected). Likewise when I 'select all' the sheet does not become shaded.

In addition:

Can not fill series
Can not change cell format (specifically to show Currency or Accounting w/$ symbol)

And I am sure there is much more that I have not found yet. I have not seen a single thread on this topic anywhere on the internet.

Help!

Good afternoon,

Is there a way to enter a colon into a standard number to create a value that can be formatted into a 24 hour time value ?

eg a time is listed as 1345 with a general number format, and I want it returned as 13:45 witha custom format of hh:mm.

Other than creating a table and using a vlookup function, I am hoping there is a better way?

Darren

How do I get the colors to change automatically when I use a drop down list.
Per say here is what I am trying to do

If a certain "word" is used from the drop down list I want the background of
that cell to change its color to "green." And if later I change the "word" to
another from the drop down list, it will change it's color to a specified
color.

The drop down list that I use was created from cells that have the colors
already in the "word", but I do not know how to make the list show the colors
so it puts the word & color automatically in the drop down list to the cell
with the drop down (if that makes any sense).

If there is a way, please spell it out simple enough for me to understand,
as I don't know fully the capabilities of formulas or vba.

Hi there

Please can someone help me. How do I create an IF formula to find cells where the number of hours in one cell is more than the number of hours in another cell? eg one cell has 0.40 hrs and another has 02.25 hours - I want have an IF formula to say where 02.25hrs > 0.40 then type "FAILED" but I cant overcome the greater than problem on the hours which is giving me the wrong answer.

Can you put a diagonal line in a cell and then color one part of it one color and the ther part of it another color?

Happy Day to all,

Can you please help me,

A1= time in
B1= time out
C1= time in
D1= time out

I want to calculate the late and under time,
Office start at 9:am w/30 mins Grace period,
The break time is one hour only, please include over breaktime in calculation.
End of office hours 6:00 pm, strickly no over time

I know that you can do PASTE > VALUES in order to keep your conditional formatting on an existing sheet, but sadly the people in my office are prone to not understanding this ("It's a bit technical") and so they just pasting blocks of text from elsewhere and lose it all.....

Does anyone have any suggestions about how to maintain the formatting when people just do a traditional C&P ?

Thanks