 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / November 2006Conditional Statements and Time Format
Hello, I have a spreadsheet where I need to calculate a range of time in a cell and display a value in another cell. Example is in cell F2 I have a time displayed of 15:34, and in the calculation window it displays as 3:34:00 PM. In Cell F3 I want to display one of three things, "1st shift", "2nd shift" or "3rd shift". Is it possible to use the conditional statements to give me the value of "1st shift" when cell F2 is between >= 07:00 and < 15:00? I can't find anything that speaks to getting thee range information from time. Thanks, Brian =IF(AND(F2>=TIMEVALUE("07:00"),F2<TIMEVALUE("15:00")),"1st Shift","") > Hello, > [quoted text clipped - 12 lines] > Thanks, > Brian This works too, thanks. Brian > =IF(AND(F2>=TIMEVALUE("07:00"),F2<TIMEVALUE("15:00")),"1st Shift","") > [quoted text clipped - 14 lines] >> Thanks, >> Brian Try something like this: With F2: (a time value) This formula returns the corresponding shift F3: =CHOOSE(INT(MOD(A1-7/24,1)*3)+1,"1st","2nd","3rd")&" shift" Where: Shift 1 is from 11 PM (inclusive) to 7 AM (exclusive) Shift 2 is from 7 AM (inclusive) to 3 PM (exclusive) Shift 3 is from 3 PM (inclusive) to 11 PM (exclusive) Is that something you can work with? *********** Regards, Ron XL2002, WinXP > Hello, > [quoted text clipped - 12 lines] > Thanks, > Brian Ummmm....typo (sorry) The formula should refer to F2 (NOT A1) F3: =CHOOSE(INT(MOD(F2-7/24,1)*3)+1,"1st","2nd","3rd")&" shift" *********** Regards, Ron XL2002, WinXP > Try something like this: > [quoted text clipped - 32 lines] > > Thanks, > > Brian Works great Ron, thanks. Brian (at home address) > Ummmm....typo (sorry) > [quoted text clipped - 43 lines] >> > Thanks, >> > Brian
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.