Question about dates/times

Is there an efficient way to format columns for the date, time, and day of 
week, and then reference that data in statements, for example using an if 
statement assume a certian value for each month/day of week/time of day? 

0
mchaleb (1)
11/7/2007 2:34:02 AM
excel 39879 articles. 2 followers. Follow

2 Replies
530 Views

Similar Articles

[PageSpeed] 45

That is a very ambiguous question.

Firstly, it is simple to format a column, just select the column, and use 
Format>Cells>Date and Time and choose a format.

But what does '... an if statement assume a certain value for each month/day 
of week/time of day...' mean?

-- 
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Brendan" <mchaleb@hotmail.com> wrote in message 
news:e%23bdTcOIIHA.588@TK2MSFTNGP05.phx.gbl...
> Is there an efficient way to format columns for the date, time, and day of 
> week, and then reference that data in statements, for example using an if 
> statement assume a certian value for each month/day of week/time of day? 


0
bob.NGs1 (1661)
11/7/2007 3:22:04 AM
First - the way a cell is formatted doesn't affect the underlying value, 
which is what other cells will use in calculations. So how the IF() 
statement interprets the value doesn't depend at all on how the cell is 
formatted.

One way:

A1:     <date>

Format as   "dddd dd mmm yyyy hh:mm:ss"

What "certain value" do you want to assume?

You can use

    =MONTH(A1)
    =WEEKDAY(A1)
   
or for time,

    =MOD(A1,1)




In article <e#bdTcOIIHA.588@TK2MSFTNGP05.phx.gbl>,
 "Brendan" <mchaleb@hotmail.com> wrote:

> Is there an efficient way to format columns for the date, time, and day of 
> week, and then reference that data in statements, for example using an if 
> statement assume a certian value for each month/day of week/time of day?
0
jemcgimpsey (6723)
11/7/2007 3:23:02 AM
Reply:

Similar Artilces:

Backup question
I've moved to a new pc and for some reason I thought that when you backed up, a new file was created. Am I right? At the moment, it's just backing up to the backup file. So I've just just my working file and my backup file. Please could you clarify this? Thanks again. In microsoft.public.money, abc wrote: >I've moved to a new pc and for some reason I thought that when you >backed up, a new file was created. Am I right? At the moment, it's >just backing up to the backup file. So I've just just my working file >and my backup file. P...

Date Formula
I am using Access 2003 I created a preventative maintenance (pm) database that requires the operator to enter the date when a pm operation is completed. The operators doing this are not typist and I am finding a lot of error with date data entry. Such as reversing month and day or putting in the wrong year. For example: when the date should have been 2/4/08 - the employee is typing 4/2/08 or putting in the wrong year. This date is an important field as future operations are keyed off of this date. I want to make the date field limited to "NO date that is greater then today&qu...

Time Zones & Outlook
When you change the Time Zone on your PC, All-Day events span two days. The MS workaround is to change the Start- End dates on each and every event/appointment. As this is not exactly the 'best' workaround, does anyone have a better one? Thanks!! Have you also seen the following MSKB article? http://support.microsoft.com/default.aspx?scid=kb;en-us;290835&Product=out -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:020e01c3b847$48642fa0$a401280a...

Today's date in Chart
I created a dynamic gantt chart in Excel 2007 which changes based on the current date "=TODAY()". Is there a way to have the current date automatically appear somewhere in the produced bar chart as a "legend"? Thanks. I tested this in xl2003, so you'll have to retest... I added a textbox from the Drawing toolbar into my chart. With the textbox selected, I typed: =a1 in the formula bar Then I typed: =today() in A1 And it seemed to work ok. I changed the numberformat to other date formats and that was reflected in my textbox in the chart, too...

Question about Xml Schemas "qualified" and "unqualified"
In the textbook, there is a sentence that "Default XML namespaces(xmlns="...") helps a lot, but can also create problems, as a side effect of the rules for automatic qualification. How to understand "automatic qualification" here? Could you please give me an example? Secondly, unless otherwise specified, a schema prescribes that loal elements and attributes must be "unqualified". What does "unqualified" mean? Could you please giv eme an example? Finnaly, could you please tell me what's purpose of using these two things? Thanks a lot! ...

a question and a question
What is the correct name of the type of selection box used in "customizing Word", for example.... you select a word or operation from a list on the left pane and move it over to the right pane. Is there a template to build one of these? thanx You will have to explain more in detail what you are trying to do. -- Stefan Blom Microsoft Word MVP "cliffordjf" <cliffordjf@discussions.microsoft.com> wrote in message news:9856CCA7-8A8E-440C-A0D4-76EE4FA644E2@microsoft.com... > What is the correct name of the type of selection box used in "c...

IF AND question
Hi there, I need a function that can provide one of three answers: 1 2 1 1... 3 3 4 4... ? ? ? ?... If A1 = 1 and A2=3, answer 106; but if A1=1 and A2 = 4, answer 104; but if A1=2, regardless of A2, answer 95. The next function for column B is the same, except the answer is dependent on the value delivered from the column A function. E.g. A3 + 6 or A3 +4 or A3-5 etc. Any help would be most appreciated. -- ***** Many thanks Gamq Use the below formula for your first query. =IF(A1="","",IF(A1=2,95,IF(AND(A1=1,A2=3),106,IF(AND(A1=1,A2=4),104)))) ...

IE8 privacy question
I am running XP-Pro SP3 and considering upgrading from IE7 to IE8. I have read all the feature and benefit articles from MS, but have one question that remains unanswered. In IE8 there is mention of being able to restore previously viewed websites or something to that effect. I think this is on a drop down menu somewhere. 1) Does In-private viewing prevent this? 2) Does manually deleting your complete browsing history clear this? 3) Is there a registry entry that can prevent this action? 4) Is there a group policy change that can prevent this action? In essence for privacy pur...

Question about Paste Special
Hi, I have a problem sometimes with the paste special options when goin from one excel workbook to another. For example, sometimes when I cop data from one workbook and then paste special into another, I get th options that include: All, formulas, values, formats, has the option t transpose the data and paste link among other options. And the sometimes I try and paste data to another workbook and I end up th paste special options: Bitmap Image Object, picture, bitmap, and I als lose the ability to paste link. Well you can do it, but it puts it i as an object. What I want is the first past...

Microsoft Query question
I am trying to use Microsoft Query to get data from my SQL 2000 databases. The problem is that I have a few columns with names that conflict with keywords (like Identity). (Yes. I kow that keywords should be avoided but it is done and cannot be changed.) This is causing a keyword syntax error within the Query application. Oddly enough, even if I do not select the column the error still appears. And when you look at all the columns available, the keyword named column shows up in boldface. When I am doing queries in other programs I put square ("[ ]") brackets around column and table...

Question
Why did the chicken cross the road? -- Dr. Stephen Hopkins, MD "Dr. Stephen Hopkins, MD" <DrStephenHopkinsMD@discussions.microsoft.com> wrote in message news:B8434E21-DDA3-44D7-B39B-CD5A8C33A7BD@microsoft.com... > Why did the chicken cross the road? > -- > Dr. Stephen Hopkins, MD To collect her email, why else? Dr? You should be ashamed to put such a title on an idiotic off topic post !! -- Regards Steve. MS-MVP. MAIL. [DTS] UK. http://www.getsafeonline.org/ mac;1266180 Wrote: > "Dr. Stephen Hopkins, MD" <DrSte...

Calculating time #3
If i add time when it reaches 24 hours it starts back at 0. i would like it to contunie adding the hours to get a total hours. format as [h]:mm so that your do not roll over into days. -- --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Mike" <Mlundblade@Avon.org> wrote in message news:1fa4c01c4589d$53b3f970$a601280a@phx.gbl... > If i add time when it reaches 24 hours it starts back at > 0. i would like...

custom dates sorting alphabetically
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel This question was asked but not answered in 2007 (the posted answer didn't work for me...) <br><br>When I try to sort contacts by a custom date it sorts the dates alphabetically- that is, Fridays first, WEdnesdays last. This is hash. I looked in Entourage as well as system preferences to change the date format but could not find a way to do that. In any case, how can MS have a date field sorted alphabetically in the first place???????? <br><br>Help! ...

Highlighting specific dates in a graph e
I have a 2d line graph with 2 series of data graphed against a time axis. I need to highlight points on both series at specific dates with the date data from another sheet. Excel 2000. --- Message posted from http://www.ExcelForum.com/ How will you highlight the points? If you want to place a line (like a gridline) at a given date, try a combination of these two techniques: http://peltiertech.com/Excel/Charts/AddLine.html http://peltiertech.com/Excel/Charts/ArbitraryGridlines.html If you want a label on the appropriate points, use formulas to put the labels into cells in a range ...

OWA Question #16
Hello All: Quick Question regarding OWA. We are about to finalize migration from 5.5 to 2003. We have an existing web presence already in DNS a www.mydomain.com. and running on existing web servers. With 5.5 natrually, to access OWA, it was www.mydomain.com/exchange. This will not be possible now since OWA runs off of the Exchange server instead of relying on our web server under 5.5. What's the easiest way to overcome this. Thanks If you can afford the extra license, you'll probably want to run a front-end server so no one is connecting directly to the Exchange database s...

401K questions
I just started a 401K at work and I'm wondering how to deal with it in Money. I've got my paycheck split into different categories currently and now I have to put the 401K amount into a category as well. I didn't see one specifically for this. What do other people use? Should I start using the 401K Manager? I haven't gone through it yet I'm just wondering if it works well and how much benefit I'll get from it. Thanks in advance! Mike You will want to create an investment account for the 401(k) and TRANSFER your contributions from your checking account to the...

Divide Dates into Weeks and Months
Hi All, I have a table that has 3 fields: OT_DATE WEEK MONTH The purpose of this table is to calculate overtime worked per employee per week. I join to the table to an attendance table on the date and then use this tbale to group by week (so that I can caluclate the hrs over 40 for each employee). Is there any way to do this other than manually adding dates to this table. It is no big deal, but I didnt know how other people did this. ...

VBA//Oracle Interfacing Question
All, I have been able to correct to my database but I have one question question: Within objSession I want to list all available tables and all available views. How exactly is this done? thank you. Set objSession = CreateObject("OracleInProcServer.XOraSession") Set objDatabase = objSession.OpenDatabase("", "User/Pass", 0) On Nov 16, 12:05=A0pm, jason <jason.mell...@gmail.com> wrote: > All, > > I have been able to correct to my database but I have one question > question: > > Within objSession I want to list all avail...

Text Box Question
Greetings, I have been using Visio for years, however, something has happened and I can't figure out how to undo it. Basically up until today when I added a text box and entered text, the text went horizontal as it was typed. Today when I add a text box and enter text, the text goes vertical. How do I get back to the old behavior? TIA using text container shape adjusting tool (text block tool), make the text container shape wider horizontally. Have you used Asian text font recently?...check help for "vertical text" "Ray Batig" wrote: > Greetings, > &g...

If / Then Question
How would I do this: IF A1="N" then I need A2 and A3 to="N/A -- pkniven ----------------------------------------------------------------------- pknivens's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2767 View this thread: http://www.excelforum.com/showthread.php?threadid=47531 pknivens Wrote: > How would I do this: > > IF A1="N" then I need A2 and A3 to="N/A" Hi pknivens In A2 and A3 use this formula > =IF(A1="N","N/A",""), this will return blank if A1 is not N, if you want it...

Setting Due Date in Phone Activity
Hi, How to set Due date to say next day by default in PhoneCall Activity? Also need to add some default value to other field, how to do that? Thanks Ken ...

ROWCOUNT question
Hi All, The below is a UDF that returns the ROWCOUNT for a table. I need to know that value for WHILE loop. How do I take the Returned value from the UDF and load it intio a variable in the SP where it was called from? Or maybe there is another way. ALTER FUNCTION [dbo].[RecCount] ( @TableName CHAR(15) ) RETURNS TABLE AS RETURN ( SELECT Rows FROM sysindexes WHERE id = OBJECT_ID(@TableName) AND indid < 2 ) Thanks, Eric Eric S (xxx_noSpam@Hotmail.com) writes: > The below is a UDF that returns the ROWCOUNT for a table. I need to know > that va...

Dates in Chart Legends
I'm using dates in the horizontal legend of a column chart. On the data source, there are only columns for the days we actually worked, i.e. there are no columns for the dates for Sat, Sun, Holidays, etc. There are no hidden columns in the sheet. When I select that area for the horizontal legend and view the chart, all dates are displayed in the legend, leaving gaps in the chart. I remember a similar problem in Excel 2003. What am I missing? Thanks. -- Hal the CFO My guess is that the data on the worksheet is formatted "ddd" or "dddd" to show days-of-the-w...

Finding Previous Date(s)
I am looking for the best way to find the previous date of treatment from any date. I have a table that hold dates representing the last time a treatment was made to a particular entity. These entities are related by a super-grouping (several even though I only show one below). When treatments are made an entity may or may not be treated within a super-grouping. SupGrp EntitiyId Date 1 A 9/10/2007 1 B 9/10/2007 1 C 9/10/2007 1 B 9/15/2007 1 A 10/10/2007 So let's ...

Exmerge Question #7
When I run Exmerge on a mailbox on my 2003 server to export to a PST, it will delete all emails after the copy. I thought EXmerge would "copy" not delete. is this the default? If so how can I just have it do a copy out to the PST and leave the mailbox alone. thanks Rick in "options", make sure "archive data to target store" is not selected on the "Import procedure" tab... -- Susan Conkey [MVP] "Rick" <drummer10980@gmail.com> wrote in message news:1165509540.777142.38260@16g2000cwy.googlegroups.com... > When I run Exmerge on ...