Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Force a cell to contain a specific time
Hi,
I have a spreadsheet to track staff who work holidays/overtime hrs. A B C D HOLIDAY Start Time End Time Pay 1 New Yr Eve 8:00 PM 12:00 AM Overtime 2 New Yr Day 12:00 AM 7:00 AM Regular time So for any holiday that is an "Eve" I want to force the end time to be 12:00 AM since the pay is overtime up until 11:59PM. I created another field to force the 12:00 AM time into the End Time field that logically seems to make sense but is not calculating correctly... =IF(A1="New Yr Eve", C1=12:00 AM, " ") End Time is currently a drop down list with values 12:00AM - 11:59PM. Thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Force a cell to contain a specific time
First problem: you cannot use a formula to put data into another cell. So
putting C1= into the If statement won't help. You must enter this If statement in cell C1. Formulas affect only the cells they are entered into. Second problem: Excel does not understand 12:00 AM in an If statement. There are more than one way of putting this time into a cell, but I like: Hour(0,0,0). Third problem: If A1 doesn't have "New Yr Eve" in it, what do you want in the cell? Right now you are specifying a blank. But you won't be able to do arithmetic on the blank. I expect you will want some other time rather than blank. So your If statement will look something like: =if(a1="new yr eve",time(0,0,0),"what you want here if it's false") Regards, Fred. "jrt" wrote in message ... Hi, I have a spreadsheet to track staff who work holidays/overtime hrs. A B C D HOLIDAY Start Time End Time Pay 1 New Yr Eve 8:00 PM 12:00 AM Overtime 2 New Yr Day 12:00 AM 7:00 AM Regular time So for any holiday that is an "Eve" I want to force the end time to be 12:00 AM since the pay is overtime up until 11:59PM. I created another field to force the 12:00 AM time into the End Time field that logically seems to make sense but is not calculating correctly... =IF(A1="New Yr Eve", C1=12:00 AM, " ") End Time is currently a drop down list with values 12:00AM - 11:59PM. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Force excel to open on a specific sheet | Excel Worksheet Functions | |||
IF statement to calculate time usage in specific time bands | Excel Worksheet Functions | |||
Force user to enter data in cell before moving to next cell | New Users to Excel | |||
Can you force a spreadsheet to open to a specific cell reference? | Setting up and Configuration of Excel | |||
Use Validation to force specific entry of first 3 characters in st | Excel Worksheet Functions |