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
384 Views

Similar Articles

[PageSpeed] 39

=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:

Fixed Formating
I have every other row highlighted. When I sort, the sort takes the row format so that rows are no longer highlighted every other row. Is there a way that I can fix/lock the formating by row? Thanks Hi have a look at http://www.cpearson.com/excel/banding.htm -- Regards Frank Kabel Frankfurt, Germany Ronbo wrote: > I have every other row highlighted. When I sort, the > sort takes the row format so that rows are no longer > highlighted every other row. Is there a way that I can > fix/lock the formating by row? Thanks ...

Trouble formatting Date data
I am using a Line chart to plot specific milestones over a series of dates. Each line in the chart represents the series of data and its progression over time. I have managed to create each series of data with my dates showing up in the X axis as a Time-Scale. However, I need my categories (the names of the milestones) to appear in the Y axis and I can't figure this out. I also want to experiment with having the dates appear in the Y axis, and categories in the X, although this does not appear to be possible. I believe this would represent my data more meaningfully. Your help i...

group-object formatting question
Hello there, one question on the formatting of the group-object output. Using dir | Get-Acl | ForEach-Object { $_.Access } | Group-Object IdentityReference I get a table with Count, Name and Group. Count Name Group ----- ---- ----- 9 predefines\Administr... {System.. 9 grpTP-FILES-IT-RW {System.Secur How can I access just the Name field of this output? Thanks very much, Uwe R2V0IHRoZSBwcm9wZXJ0eSB2YWx1ZSBmcm9tIGVhY2ggW01pY3Jvc29mdC5Qb3dlclNoZWxsLkNv bW1hbmRzLkdyb3VwSW5mb10NCg0KZGlyIHwgR2V0LU...

need help with an if statement
right now i am using this code on a command button; Sub Save_As_FileName() FName1 = Range("d3").Value FName2 = Range("d5").Value Fname3 = Range("d6").Value Fname4 = Range("d7").Value pth = "f:\bids\" MyFileName = FName1 & " " & FName2 & " " & Fname3 & " " & Fname & " " & ".xls" ActiveWorkbook.SaveAs Filename:=pth & MyFileName End Sub But the pth will change, depending on what is in cell d2. so, I think i will need an If statement saying if cell d2 = ...

Long Save Times in Publisher 2002 sp3
Hello all. I work in a hospital doing general PC and printer support and I was hoping you folks could help me resolve Publisher 2002 problem an HR employee is having. She is working on a newsletter (.pub, 160 MB) and when she tries to save it, it locks up Publisher (status changes to "Not Responding") and takes about 7 minutes. She is saving the document to a network drive, but the same thing happens if you try to save it locally. In an effort to resolve the problem, I ran Disk Cleanup, AdAware, Defragmented the drive and upgraded Publisher from 2002 sp1 to 2002 sp3. Before I did th...

Open and Convert Mac .numbers to excel file format tools
Are there any tools that allow PC user of Excel to open a Mac .numbers extension file? kspalladino@yahoo.com ...

Formatting Task Description
I don't know how to add the 'ruler' for formatting my task descriptions Click "View ruler" in upper right corner of Note area. davedel2001 wrote: > I don't know how to add the 'ruler' for formatting my task descriptions ...

how I can format a number like hundreds, thoudsands
I am from INdia and need to format the numbers in Indian style like comma sepration based on hundreds, thousand pattern Very first hit on Google searching for Format and Indian:- From a Google search here is a reply by David Richie to the same question in January If you have Excel 2002 or later look up bhattext in help. Otherwise the following format might help you [>=10000000]##\,##\,##\,##0.00;[>=100000]##\,##\,##0.00;##,##0.00 (This format separates groups for India/Thailand, format valid for positive numbers up to 99,99,99,999.99 or 999,999,999.99) in Excel XP see function Bh...

Formatting Date in Header or Footer
How can I change the default date format (8/18/2004) in the header/footer to a different date format (18-Sep-04)? Is there a way without using VBA? Thanks Hi see your other post -- Regards Frank Kabel Frankfurt, Germany "Emile Zac" <emilezac@hotmail.com> schrieb im Newsbeitrag news:u16wacYhEHA.3320@TK2MSFTNGP11.phx.gbl... > How can I change the default date format (8/18/2004) in the header/footer to > a different date format (18-Sep-04)? Is there a way without using VBA? > > Thanks > > ...

Newbie Time Question
I have a group of employees that work in shifts (24 Hours). Over th course of four days we had a "special event" occur. How can I create formula that spans a four day period & any employee that worked hour during that four day period would be totaled separately? Example: Special Event was Thursday Sept 2nd 11:00am through Monda Sept 6th 10:00pm. One employee worked 7:30am Thursday Sept 2nd through 4:00pm Thursda Sept 2... Equals 5hours worked during "special event´┐Ż. I am only usin time in & time out for the data. I understand formulas somewhat, but not time &...

How to calculate a Time Difference
I currently am looking to be able to subtract two times (using the 24 hour clock), where the result is a Decimal. For example. I leave at 14:00 and arrive at 17:30. Thats a difference of 3.5 hours. I believe I have this solved with the following formuals: =(C2-INT(C2))*24-(B2-INT(B2))*24 (WHERE C2 is the arrival time and B2 is the DEPARTURE Time). My problem is that if you leave at 23:30 and don't arrive until 03:30, then it gives me a large negative number instead of 4.0 hours. Any ideas? Thanks, Try: =(C2-B2+(B2>C2))*24 And format the cell as #.0. For more info on su...

insert running time on excel
Hi, Is there any way to see the current time in excel? cant seem to find an available function in excel. I know that this might require some VBA scripts as I would like to see the seconds "ticking" on a sheet. Any help or suggestions will be appreciated. Thanks function: =NOW() shortcut for non-dynamic entry of current time: CTRL+: (hold CTRL+SHIFT and tap ; key) best wishes -- www.stfx.ca/people/bliengme "hungry burger" wrote: > Hi, > > Is there any way to see the current time in excel? cant seem to find an > available function in excel. > &...

Adding time to date-time formatted cell
Not sure quite how to do this... I have a cell (A1) which I have formatted to read the date and time (MM/DD/YYYY HH:MM AM/PM). That is my starting time. What I want to do now is be able to input a time period in hours (A2) and have another cell (B1) add those hours to A1 so B1 will read the "ending time" - formatted with the (MM/DD/YYYY HH:MM AM/PM) format. Is this possible? Thanks! Basically, I want it to lay out like this: Cell A1: 8/12/05 2:00am Cell B1: 12 (it takes 12 hours to run this process) Cell A2: 8/12/05 2:00pm Thought that might make it more clear what I&...

2003 OWA
Hi there,We have been noticing that the images which make up the mailbox user interface are not being displayed to users over the weekend period like an access restriction is in place.This renders the interface useless and I cannot find a setting anywhere that could be causing this.Can anyone offer any advice? Thanks in advance. Colin I don't know of any way to make this kind of setting in Exchange or IIS, so I have to believe that you have a firewall between the client and server that's causing this problem. -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and...

If statement depending on NAME of workbook
I would like certain events to occur depending on what the name of the workbook is. Is there a way to do this? What would be the code for this? Can something like if WorkbookName = "Name" Then Or am I totally out of whack here? Rob rob, You can use either ThisWorkbook.Name or ActiveWorkbook.Name e.g. If ThisWorkbook.Name = "Test.xls" Then ' do something Else ' do something else End If John "rob nobel" <robnobel@dodo.com.au> wrote in message news:eygDEsaqDHA.688@TK2MSFTNGP10.phx.gbl... > I would like certain events to occur depending on wh...

Time Macine
I have a client that sends me emial and his emial shows up and hour later then when he acutally send the message. I checked: Server time Server Time Zone My Server Time Zone My Time Zone Outlook Time Zone Is there one more place that the Time zone could be mis-configured? -- - I am Johnny you can set a time in outlook to delay the delivery of messages "Johnny" <alphascooter-verizon@yahoo.com> wrote in message news:MPG.1ba912df9fcc39049896bc@msnews.microsoft.com... > I have a client that sends me emial and his emial shows up and hour > later then when he acutally ...

Mail not showing up in Outlook on time
I am using Outlook 2002 with Exchange 2003 on Windows XP Pro. Problem: User is not getting email on time. When a mail is sent to the user it dosn't show up in the inbox until you move around in Outlook (example: you move from contacts to inbox or from inbox to calandar) When you move around in outlook all of the mails now show up in the inbox. Things I have tried: Checked all mail setting with another 2002 client that is working. Using OWA checked to see if the mail is getting to the mailbox, it is. Any other suggestions you guys can think of? Thanks Troyd I am seeing the same...

SumIF-multiple conditions/OR
I have a range of cells that I want to ck for a "Y", then in the next range can be "STOP" or a value of "Go"? Kinda a mix of conditions. A bit more detail, perhaps. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "GaryW" <GaryW@discussions.microsoft.com> wrote in message news:4191FD5D-C409-47AD-87ED-EE077D9A7D6B@microsoft.com... >I have a range of cells that I want to ck for a "Y", then in the next range > can be "STOP" or a value of "Go"? Kinda a mix of conditions. &quo...

Storing data in Short Date format
Hi, I have produced a couple of other Access databases where, in the Table design, I have selected Short Date and only the date has been stored in the table. In the current database I am working on I have selected Short Date on table fields but find that the date AND time is being saved. This is causing problems whem I want to seach by date. Can anyone suggest why, on this occasion, Short Date format is causing date and time to be saved (like the General Date format) when data is entered through a form (where the control is also set to Short Date format)? The problem is evident on all t...

Pull Members' Race Times from a Database?
Please help: Sheet 1 is a spreadsheet with 1/2 marathon race results. Column A has all the names (Last and first in the same cell, sorted alphabetically by last name). Column K has the finishing times. Sheet 2 is a list of our club members names. Again, last and first names in the same cell, sorted alphabetically. I am trying to use VLOOKUP to pull any members' finishing times from sheet 1. However it is not working correctly. Times appear for those who did not run the race (I want NA to show) and some members who did run the race have the wrong time next to them. Trying FALSE or ...

stop number font size from changing each time i click on somethin.
i am trying to do a graph of club lockers (bowling club) each time i put in a locker number the size changes back to 10. how do i get the no. size to stay at the size i want. using excel. I'm guessing that your default font size in the Normal Template is 10. Do you want to change the default font size for all Excel docs or just this one? "alibob26" wrote: > i am trying to do a graph of club lockers (bowling club) each time i put in a > locker number the size changes back to 10. how do i get the no. size to stay > at the size i want. using excel. ...

Problems moving Microsoft Date and Time Picker Control on MFC dialog
I've got an MFC CDialog class that displays and manages a dialog. There are several controls on the dialog (combo boxes, edit boxes, static text boxes, etc.). Included in the mix are four Microsoft date and time picker controls. The dialog is used in three separate instances. Not all of the controls are relevant in all three instances so we hide the controls that aren't relevant in each instance, move the controls around on the screen, and resize the dialog to eliminate dead areas on the dialog. We are getting the CWnd for each control and using the SetWindowPos method for each control...

how to calculate between times
hello all can someone pls advice. i need to calculate the time between start to finish dates. A1 08/07/05 12:00 A2 09/07/05 1200 i found this formula =text(a2-a1,"h:mm") to be helpful. but i want to calculate business hours only which is from 9am to 5:30. can someone pls advice what formula i can use? thanks -- noelf ------------------------------------------------------------------------ noelf's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6979 View this thread: http://www.excelforum.com/showthread.php?threadid=386045 Try this =17.5/24-(A1-R...

Number format in queries
Hopefully this is easy. Does anyone know if it is possible to change the default number format for number fields to "standard" (or any other format). I'm getting very tired of going into properties all the time to flip the setting so that large numbers are actually readable. Thanks, Tom. "Tom Telford" <TomTelford@discussions.microsoft.com> wrote in message news:058E7F53-4280-4EA4-A80B-C832F4017BC5@microsoft.com... > > Does anyone know if it is possible to change the default number format for > number fields to "standard" (or any other f...

regarding system idle time
Can anybody help me to find the API call for getting the system idle time -- victorrajmr ------------------------------------------------------------------------ Posted via http://www.codecomments.com ------------------------------------------------------------------------ check out for this api [GetLastInputInfo] -- With Regards Alok Gupta Visit me at http://alok.bizhat.com "I Believe this will Help" "victorrajmr" <victorrajmr.1nbudr@mail.codecomments.com> wrote in message news:victorrajmr.1nbudr@mail.codecomments.com... > >...