Conditional 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

0
robofanuc (7)
11/21/2006 6:39:44 PM
excel 39879 articles. 2 followers. Follow

5 Replies
421 Views

Similar Articles

[PageSpeed] 32

=IF(AND(F2>=TIMEVALUE("07:00"),F2<TIMEVALUE("15:00")),"1st Shift","")

<robofanuc@yahoo.com> wrote in message
news:1164134384.820512.88890@k70g2000cwa.googlegroups.com...
> 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
>


0
11/21/2006 7:34:08 PM
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


"robofanuc@yahoo.com" wrote:

> 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
> 
> 
0
11/21/2006 7:55:01 PM
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


"Ron Coderre" wrote:

> 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
> 
> 
> "robofanuc@yahoo.com" wrote:
> 
> > 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
> > 
> > 
0
11/21/2006 8:10:02 PM
Works great Ron, thanks.

Brian (at home address)

-- 

"Ron Coderre" <ronREMOVETHIScoderre@bigfoot.com> wrote in message 
news:E036B063-B0D4-463C-865E-3486DCC4C5B4@microsoft.com...
> 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
>
>
> "Ron Coderre" wrote:
>
>> 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
>>
>>
>> "robofanuc@yahoo.com" wrote:
>>
>> > 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
>> >
>> > 


0
nomail568 (29)
11/21/2006 11:30:27 PM
This works too, thanks.

Brian

-- 

"Bob Umlas" <Excel_Trickster@msn.com> wrote in message 
news:%23BXpEQaDHHA.4132@TK2MSFTNGP04.phx.gbl...
> =IF(AND(F2>=TIMEVALUE("07:00"),F2<TIMEVALUE("15:00")),"1st Shift","")
>
> <robofanuc@yahoo.com> wrote in message
> news:1164134384.820512.88890@k70g2000cwa.googlegroups.com...
>> 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
>>
>
> 


0
nomail568 (29)
11/21/2006 11:32:22 PM
Reply:

Similar Artilces:

Where is the Keep Text Formatting feature located in Word 07
I believe this Keep Text Formatting feature might be what I need, but I have been unable to locate exactly where it is located in Word 2007. I'm trying to rid a Word document sent to me of tables, text boxes, graphics and all other document formatting, while retaining the document's text content. It is unimportant to me whether the text formatting is retained or not. Thanks. Are you referring to a Keep Text Formatting feature in an earlier version of Word? I wonder whether what you're looking for is "Paste Unformatted," since you seem to be saying you _don...

Conditional format #12
Hi. I am trying to set a conditional format. If NETWORKDAYS(A1,TODAY())> 3 I would like the cell to format with red shading. How do is set the formula in the condtional statement? I tried ="NETWORKDAYS(A1,TODAY())>3" , but this doesn't seem to work. Thanks, Mike. Hi Mike! It doesn't work because the NETWORKDAYS function is in another workbook (the Analysis ToolPak add-in). You can use a workaround by putting your condition in a separate cell (eg B1) and then referring to that cell: B1 =NETWORKDAYS(A1,TODAY()) Then your conditional format for A1 becomes:...

If Statement and Multiple Criteria
I am using a formula to bring back some text about some data. I am using the following formula: =IF(N12>M12,"is larger for girls","is larger for boys"), so that if the data in cell N12 is greater than the data in cell M12 then it says "is larger for girls" and vice versa. This works fine, apart from if negative numbers are in the cells then it brings back the value closest to zero - rather than the largest negative number. Does anyone know how to get round this. In addition - I would ideally like to alter the formula so that it says that if the two...

formating color
hi i have a formating question. i have cells with number choices i them. for example the cell looks like this. 100, 105, 123, 041, 514, 455 now i want to go back and select one of these. i wanted to highligh the one that was chosen. but it will not let me. is there a way i ca do this? it will only allow me to highlight the entire cell, not jus parts of it. thank you in advanc -- RAPPE ----------------------------------------------------------------------- RAPPEL's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3624 View this thread: http://www.excelforum.com...

How can I customize the Filter Drop down on the formatting toolbar
I want to clear everything out of it, except the customized filters I have created. Can i do that? Project > Filter by > More Filters, then select each one, Edit, and remove the checkbox for Show in Menu. - Andrew Lavinsky Blog: http://blogs.catapultsystems.com/epm > I want to clear everything out of it, except the customized filters I > have created. Can i do that? > I've tried that - it doesn't affect that drop down filter list - whether they are checked or not. "Andrew Lavinsky" wrote: > Project > Filter by > Mo...

Planning Lead time-Item Vendors Maintenance window
Previously used manufacturing and MRP in v9 and the Planning Lead time on the Item Vendors Maintenance window (bottom left corner), we used to enter 7 for the number of days in 1 week. MRP then suggested to place PO's using these 7 days as one week. Now when we enter 35 for example ( V9 calculated 5 weeks for placing the PO) Now since V10 it looks like it is calculating 7 weeks lead time (35 days divided by 5 days) Did something change with V10 on this field and how MRP uses it with the Request Resolution window? thanks! -- Doug The system now looks at calendars and can conside...

UTC time to Central Time (US)
Hello. I need to convert UTC time to US Central Time Zone. The date is in one column and the time is in another below. A | B ---------------------------------- Date | Time ---------------------------------- 2005-10-24 | 12:15:00 I want to convert the Date and Time columns in UTC to a single Date and Time column in US Central Time. Thanks for any advice Assuming UTC is the same as GMT then the difference is 6 hours STD, so if the time is 06:00 AM it is 12:00 PM GMT, thus add 6 hours and since the y are all numbers just add =A1+B1+(6...

Formatting
I received a pub doc from a third party. It was formatted as a 11x16 size. Once I decreased it to 8.5x11 and view the doc under 'print preview' the entirety of the image did not view. Basically, the image didn't shrink with the size. Any input? Patty wrote: > I received a pub doc from a third party. It was formatted as a 11x16 size. > Once I decreased it to 8.5x11 and view the doc under 'print preview' the > entirety of the image did not view. Basically, the image didn't shrink with > the size. Any input? Publisher doesn't resize the contents o...

Time duration formula
I would like to know the formula to work out my employees employment duration, from a given start date to the current day. many thanks Take a look at DATEDIF. Explained at http://www.cpearson.com/excel/datedif.htm -- HTH RP (remove nothere from the email address if mailing direct) "Ska" <Ska@discussions.microsoft.com> wrote in message news:B10818AE-1CAD-4CE5-BDED-BC0DDC96C876@microsoft.com... > I would like to know the formula to work out my employees employment > duration, from a given start date to the current day. > > many thanks Thanks Bob, I have us...

Outlook hangs ,7th time :-(
Hi all, In our company we are using Exchange 2000 as mail server, and outlook 2002 sp2 as email client. Each client is configured to download its emails to a local pst file. We are facing the following issue : If you add a rule to move messages from inbox to a particular folder, during send/receive and just after receiving 3-5 messages (and these messages are moved to their folders) the outlook stops responding. If you run outlook with /safe parameter it will not hang! If any one could advise I would be gratefull. Thanks, Osama. ...

Does Multi-Field Index Work For Date/Time Values Only
I created a MS-Access DB table with the following 3 columns: ColID - PRIMARY KEY Col1 - Number Col2 - Date/Time Next I created a multi-field index using Col1 & Col2. I entered the following row in the 1st row: 1 5/10/2007 Next when I tried to add the above row again, as expected, I wasn't allowed to enter the same row. Next I entered the following 2 rows: 1 5/11/2007 2 5/10/2007 Both the rows were accepted. After this I deleted the 3 records, went back to the design view & changed the data type of Col2 from Date/Time to Number keeping the multi-field index...

"Configuring Microsoft Office Basic 2007" every time I start
I added memory to my PC and Office 2003 started to misbehave (and it could not find a CAB file that was on the CD) so I removed it and upgraded to Office 2007. Now every time I start my PC it spends time configuring itself. I might add iTunes is the same. It means starting the PC is an even more laborious process than usual. Oh, and I have some annoying update to XML something or other that does not install either, and does not go away. It is ironic that the extra memory was to improve perforamnce. Every step forwards involves two steps back. Apart from swapping to App...

Formatting multiple object types in output from a function?
I'm stumped on this one, and have searched and searched for a solution or how-to with no luck. Perhaps it just can't be done. I have a function that may or may not return objects of multiple types. I've setup the type and formatting information for each of the individual types in format.ps1xml files, and when only 1 type is returned by the function the formatting works as I would expect. However, if two or more types are returned by the function, only the formatting for the first object type is applied and then the other types are displayed however PoSH deems ap...

Conditional Format #20
I see that I can only use 3 conditional formats. I need a few more than 3 is there any way to do this. Kevin Try this free add-in on Bob Phillips' website: http://www.xldynamic.com/source/xld.CFPlus.Download.html Another possibility (albeit limited) is to combine your conditional formats with custom formatting. See J.E. McGimpsey's site for an example: http://www.mcgimpsey.com/excel/conditional6.html HTH Jason Atlanta, GA >-----Original Message----- >I see that I can only use 3 conditional formats. I need a few more than 3 is >there any way to do this. > >Kev...

SQL select statement question
My dataset has 3 columns: customer id, store location, charges I'd like to write a single select statment that will show each customer id once, with the store location where they have the most charges, and the sum of the charges for that store location. This gives the max charges for each customer, but doesn't give the location: select a.custID, max(new.amt) from smallchg a inner join; (select custID, location, sum(charges) as amt from smallchg group by custID, location) as new ; on a.custID= new.CustID group by a.CustID thanks. joel Try this -- SELECT TOP 1 ...

Condition navigating between Controls
I have a form in access 2007, with several controls. I used the On Exit Event Procedure for navigating among controls. Example: If Method of Payment = “Check” Then Check No.Set Focus End If If Method of Payment = “Credit Card” Then Credit Card Name.Set Focus End If End Sub And so on for all controls. Form functioned well for months, however today went wild. Navigation order does not execute Event Procedure it follows exactly the order of TAB ORDER DIALOG BOX. Please advice -- Bardia It sounds like the On Exit event is not firing for some reason. Put these two lines of co...

FALSE Statement
Hello, I have a formula like this K232=IF((J232="A"),-1*I232,IF((J232="B"),I232*F232,IF((J232="C"),0)) If there is nothing on J232 it displays FALSE , I would like to get rid off this FALSE and cell should be blank, i tried IF(J232=""),"" but again this arises another problem with other cell as this cell is linked another worksheet which has formula to sum K9:K232 L6=SUMPRODUCT((MONTH('sheet2'!B5:B496)=1)*('sheet1'!K9:K500)) If i put IF(J232=""),"" its considering as a value not a empty cell , how do i...

Displaying images as a result of an 'if' statement
Is it possible to write an if statement to display a picture if a cel contains a certain value? Ive got if statements nailed, its not problem with that i have, just the returning of an image, rather tha text ar a hyperlink. Eg, "if cell A1="stadium", to show a picture of a stadium in cell C1" Thanks in advance for any hel -- Message posted from http://www.ExcelForum.com Hi not possible with formulas -- Regards Frank Kabel Frankfurt, Germany > Is it possible to write an if statement to display a picture if a cell > contains a certain value? Ive got if statemen...

Adding time
I want to add time,like a sum. 12:00+12:00+12:00=36:00? I keep getting a basic time like 12:00. Could you please supply a formula. Thank you Hi In the cell with the formula, Format>Cells>Number>Custom [h]:mm Putting the [ ] brackets around h allows it to sum past 24 hours. Regards Roger Govier trucker wrote: > I want to add time,like a sum. > > 12:00+12:00+12:00=36:00? > > I keep getting a basic time like 12:00. Could you please supply a formula. > > Thank you > ...

Time Entry
A simple problem I would appreciate your help with. I have a work sheet with several cells where time is to be entered in the format hh:mm. Unfortunately a lot of users are enetering it in the format hh.mm. Is there any way I can automatically convert to the right format, or not accept the (.) but only (:). Thanks for your help. Private Sub Worksheet_Change(ByVal Target As Range) If InStr(1, Target.Text, ":", vbTextCompare) < 1 Then MsgBox "invalid entry" End If End Sub Enter the above code in the sheets module Mangesh -- mangesh_yadav -------------------...

Conditional formatting / blank cells
Hello, I need help with a Conditional Format. This is my worksheet. Row 4 A B C D E F G H I $200 $210 I want a conditional format in G4 that states if G4 is greater than or equal to F4 the fill colour is green. If G4 is less than F4, the fill colour is red. If G4 is blank, the fill colour is white. I've tried numerous combinations, but cannot seem to get this to work. Thanks torkattack. Test for the blank first. -- David Biddulph "torkattack" <torkattack@discussions.microsoft.com> wrote ...

Excel could not save all the data and formatting you recently added
Hi, One of our users sent me an Excel file of 6 MiB. It has 7 worksheets. Most of them have <100 rows and AH columns, one sheet has 13160 rows and AH columns. The large sheet has autofilter enabled, but no actual filtering is done. (yet) 4 columns have validation: they allow a list of values specified in a range somewhere else in the sheet. There is also conditional formatting. It takes >30 seconds to calculate the sheet, however there are no real calculations, just a few concatenated string. My first impression is that this is yet another example of Excel (ab)used as a database. The p...

Changing multiple appointment times
I have hundreds of appointments and would like to move them back one hour. I know there is a link in microsofts support site that lets you change the time zone without changing appointment times. However, recurring appointments are not supported by that method. Does anyone know of a way to move all my appointments back one hour? ...

time sheet template available
I am looking for a bi-weekly time sheet template (or weekly). Having problems with excel correctly computing total times (works great for one day). bsydnes, heres one, http://www.cpearson.com/excel/overtime.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "bsydnes" <bsydnes@discussions.microsoft.com> wrote in message news:CA5250B0-8F92-41BB-BB5C-04F085C009F2@microsoft.com... > I am looking for a bi-weekly time...

PDF format pagination
I have several reports utilizing Landscape layout. I followed the Report Writers Guide from the SDK with the Width at 11 and Height 8.25 or smaller. However, when I export it to the PDF format, the layout shows up Portrait. How could I change this? The export is fine in Excel with the correct margin setup. Thanks. ...