subtract one weekday

In one worksheet I've got weekdays (SUN-SAT). What I would like to do is link 
those cells to another worksheet in the same workbook, but have them be minus 
one day. For example: worksheet1 would have WED, but the linked cell in 
worksheet2 would be TUE. Is there any way to do this?

Thanks in advance
0
Utf
2/3/2010 8:01:01 AM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
952 Views

Similar Articles

[PageSpeed] 59

What type of data are SUN-SAT? Are they real Excel dates custom formatted 
like "ddd" or strings (texts)?


-- 
Regards!
Stefi



„bcags7” ezt írta:

> In one worksheet I've got weekdays (SUN-SAT). What I would like to do is link 
> those cells to another worksheet in the same workbook, but have them be minus 
> one day. For example: worksheet1 would have WED, but the linked cell in 
> worksheet2 would be TUE. Is there any way to do this?
> 
> Thanks in advance
0
Utf
2/3/2010 8:32:13 AM
Try this

=LOOKUP(Sheet3!A2,{"FRI","MON","SAT","SUN","THU","TUE","WED"},{"THU","SUN","FRI","SAT","WED","MON","TUE"})

HTH

Bob

"bcags7" <bcags7@discussions.microsoft.com> wrote in message 
news:3405DA20-E0F1-4B60-A26C-60D90CAC5C42@microsoft.com...
> In one worksheet I've got weekdays (SUN-SAT). What I would like to do is 
> link
> those cells to another worksheet in the same workbook, but have them be 
> minus
> one day. For example: worksheet1 would have WED, but the linked cell in
> worksheet2 would be TUE. Is there any way to do this?
>
> Thanks in advance 


0
Bob
2/3/2010 9:02:59 AM
I'm not sure what data type they would be.  In the first cell I entered one 
day (Mon) and auto-filled the rest of the row for a full month.  Would this 
be a string?

"Stefi" wrote:

> What type of data are SUN-SAT? Are they real Excel dates custom formatted 
> like "ddd" or strings (texts)?
> 
> 
> -- 
> Regards!
> Stefi
> 
> 
> 
> „bcags7” ezt írta:
> 
> > In one worksheet I've got weekdays (SUN-SAT). What I would like to do is link 
> > those cells to another worksheet in the same workbook, but have them be minus 
> > one day. For example: worksheet1 would have WED, but the linked cell in 
> > worksheet2 would be TUE. Is there any way to do this?
> > 
> > Thanks in advance
0
Utf
2/10/2010 6:36:01 AM
See my earlier response, that will work.

HTH

Bob

"bcags7" <bcags7@discussions.microsoft.com> wrote in message 
news:C74DFCDF-634A-4326-882D-D22D4B8ACFC9@microsoft.com...
> I'm not sure what data type they would be.  In the first cell I entered 
> one
> day (Mon) and auto-filled the rest of the row for a full month.  Would 
> this
> be a string?
>
> "Stefi" wrote:
>
>> What type of data are SUN-SAT? Are they real Excel dates custom formatted
>> like "ddd" or strings (texts)?
>>
>>
>> -- 
>> Regards!
>> Stefi
>>
>>
>>
>> "bcags7" ezt �rta:
>>
>> > In one worksheet I've got weekdays (SUN-SAT). What I would like to do 
>> > is link
>> > those cells to another worksheet in the same workbook, but have them be 
>> > minus
>> > one day. For example: worksheet1 would have WED, but the linked cell in
>> > worksheet2 would be TUE. Is there any way to do this?
>> >
>> > Thanks in advance 


0
Bob
2/10/2010 10:32:28 AM
Yes, they are strings, I'd have thought it, because real custom formatted 
Excel dates aren't fully in capitals (SUN-SAT) but Sun-Sat.


-- 
Regards!
Stefi



„bcags7” ezt írta:

> I'm not sure what data type they would be.  In the first cell I entered one 
> day (Mon) and auto-filled the rest of the row for a full month.  Would this 
> be a string?
> 
> "Stefi" wrote:
> 
> > What type of data are SUN-SAT? Are they real Excel dates custom formatted 
> > like "ddd" or strings (texts)?
> > 
> > 
> > -- 
> > Regards!
> > Stefi
> > 
> > 
> > 
> > „bcags7” ezt írta:
> > 
> > > In one worksheet I've got weekdays (SUN-SAT). What I would like to do is link 
> > > those cells to another worksheet in the same workbook, but have them be minus 
> > > one day. For example: worksheet1 would have WED, but the linked cell in 
> > > worksheet2 would be TUE. Is there any way to do this?
> > > 
> > > Thanks in advance
0
Utf
2/11/2010 8:39:01 AM
Reply:

Similar Artilces:

multiple data entries in a field from one list
Is it possible to populate a field with multipule data selections from a drop-down list? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ You can use the list box or combo box from the Controls Toolbox (not from the Forms toolbar). You can specify the MultiselectMulti or MultiSelectExtended property. You'll need code to examine the user selections. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "da...

Subtracting Cells formatted with Date AND Time to get hours?
Thanks in advance for the help. Is it possible to subtract 2 cell that are both formatted as mm/dd/yyyy h:mm to just have it subtrac the times to get a total # of hours between the 2. For example, A is 1/1/05 13:00 and B1 is 1/2/05 14:30. I'd like C1 to give me th total of 25:30. This forum is always a great place for help. Thank again in advance for your help. mcr =B1-A1 and format as [hh]:mm -- HTH Bob Phillips "mcr1 - ExcelForums.com" <miles@milesreed-dot-com.no-spam.invalid> wrote in message news:Oy629ATgFHA.4032@TK2MSFTNGP10.phx.gbl... > Thanks in advan...

Mutiple Personal Folders in Outlook
Hi all, I recently noticed that rather than just one Personal Folder in my Outlook mail folder view, there are three. Now, I have set up three separate POP3 accounts and pst files on this one workstation; however, other than the main one, each of the others opens and only shows that one. When I right-click the second or third in the list, and select Close, I get the message: Operation Failed, object could not be found. How can I get this set back to just one folder? Thanks! Larry Create a new mail profile and configure it to use your default .pst file, not one of the ghosts. --�...

how can I activate the presenter view using only one monitor?
I'm trying to use the presenter view to make a presentation with power point, so just for practice I tried to use it with only one monitor but I can't. Somewhere in the microsoft help site says it is possible, but it just doesn't happen. Please help. thnx, I don't think you can activate Presenter View if you only have on monitor, sorry. -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2007? http://www.echosvoice.com/2007.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd PowerPoint 2007 Complete Makeover Kit http://tinyurl.com/32...

multiple users of one document
Our school has been using Publisher 2003 until last week. When our newsletter (which is saved on a server) was to be edited by a teacher if it was already open by another user you were warned that the document was already open and read only. 2007 does things differently, it lets you open and edit the document and when you go to save the changes that is when it gets interesting. You can either save it over the original document or save it as another document (that gets messy). If you save it over the original, and the other user later saves their copy my editing is lost. Is there a way t...

date subtraction difficulty
I'm trying to subtract dates, to calculate time duration. I'm subtracting column A from column B into column C. Column C is formatted 'm "mos", dd "days"' and columns A and B are formatted 'mm/dd/yy/@' Unfortunately each of the durations is 1 month too long. That is, if the real duration is 4 days, column C will display '1 mos, 4 days' Any clue what I'm doing here? Thanks much. On Tue, 18 Jul 2006 11:58:10 -0500, Peter <facetious_nickname@hotmail.com> wrote: >I'm trying to subtract dates, to calculate time durati...

Multiple IF's in one cell
I would like to find a formula that will allow me to sort/find a name in several columns of names and if it is one of the columns then indicate which column heading it was under. I've tried embedded IFs but I have limited experience with using multiple formulas in one cell. Hello Michael, Are you searching for the name in a single row or several rows? If it's the former then assuming you want to search in A2:J2 and return the heder from A1:J1 try =INDEX(A$1:J$1,MATCH(L2,A2:J2,0)) where L2 contains the name for which you need to search for the latter try this to search withi...

More than one row of Sheet names?
Is there any way to view more than one row of sheet tabs? Similar to how you can move the start menu to increase the amount of rows. I have a lot of sheet name tabs and I would like to view more than a couple at a time. Thanks! AFAIK, no -- Don Guillett SalesAid Software donaldb@281.com "Sam Weber" <sam@hostradius.com> wrote in message news:6b6101c475a2$7f3380d0$a401280a@phx.gbl... > Is there any way to view more than one row of sheet tabs? > Similar to how you can move the start menu to increase > the amount of rows. I have a lot of sheet name tabs and I > wo...

legend colors ... use darkest ones; prefer specific symbols for readability
The user has asked that the darkest colors and specific symbols be used for readability of the chart: Black is the most preferred color; white is the least-preferred. Symbols to have lowest priority for use are "-" and "--" (double-wide line). Example: The first series to be plotted will have Format Data Series | Patterns settings as follows: Marker -- Style = diamond Foreground (color) = black Recording a macro for setting Style = diamond Foreground (color) = black resulted in .MarkerForegroundColorIndex = 1 .MarkerStyle = xlDiamond Is there a list of t...

subtracting units of time
Hi there, I am trying to keep track of my hours at work, as I need to make up a day I spend at hospital during the course a month. I have put together a sheet (sort of a cut-down version of my department's timesheet), with day, month, in and out. What I want to do is subtract 30 minutes from each standard work day, so I end up with 8 (or however many hours worked) minus my half hour lunch break. Any help would be greatly appreciated. Regards, Glenn Auckland, new Zealand Glenn Stick 0:30 in a cell and name it lunch_break. Then use = A1-lunch_break, where A1 contains your times be...

ploting one cell from 10 worksheets into one chart
i am trying to figure out a way to plot one cell, lets say c-1 from 10 -30 worksheets, in one workbook; on one chart. Each worksheet is a date and each c-1 value is just a number. any help would help thanks alex n. alex.nazarchuK@unitedwater.com ------------------------------------------------ Message posted from the Excel Tip Forum at http://www.ExcelTip.com/forum/ -- View and post usenet messages directly from http://www.ExcelTip.com -- Hundreds of free MS Excel tips, tricks and solutions ------------------------------------------------ You can plot each data point as a separate ser...

multiple if/then on one cell
here is basically what i want to do. if a3=b3 than x if not than check to see if a3=b4 than y if not than check to see if a3=b5 than z i just don't know how to make excel understand. if there is a different command to use that would be cool too .. i just don't know it. thanks in advance. Try this: =IF(A3=B3,"x",IF(A3=B4,"y",IF(A3=B5,"z"))) "cheshire191" wrote: > here is basically what i want to do. > > if a3=b3 than x > if not than check to see if a3=b4 than y > if not than check to see if a3=b5 than z > > i just ...

Shareing a workbook with one user
I have a workbook that I allow read only capability to nine other guys. One of these guys also needs update capability. How can I set it up to give one person update capability but the others read only capabilities. How about giving him the password - you obviously trust him to make changes. -- EdMac ------------------------------------------------------------------------ EdMac's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30736 View this thread: http://www.excelforum.com/showthread.php?threadid=525766 It is not password protected. The workbook is on my...

match one cell for another one that contains it
I'm trying to do a true/false for a cell that contains info from another cell: A1 = auto A2 = automobile This is the closest as I can get: =ISNUMBER(MATCH("A1",E2,0)) I don't know how to do a "contains" function where all of A2 contains part of A1 to set it to True. Thanks in advance for the help! This forum is great! -- krayziez ------------------------------------------------------------------------ krayziez's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34891 View this thread: http://www.excelforum.com/showthread.php?thre...

list in one cell
how can i ctreate a list in one cell such as drop down arrow You can use data validation for this http://www.google.com/search?sourceid=chrome&ie=UTF-8&q=data+validation+excel "sohail" wrote: > how can i ctreate a list in one cell such as drop down arrow hi i think you are intersted in data validation. on the tool bar>data>data validation. also see this site for tutorial. http://www.contextures.com/xlDataVal01.html regards FSt1 "sohail" wrote: > how can i ctreate a list in one cell such as drop down arrow ...

More than one instance of BP
Hello: Does anyone see an issue with creating a test environment for BP (3.0 or 4.0) by using a separate SQL instance (on the same server box)? I am thinking that the Portal could be set to point to the other named instance, but I may not be thinking of all the possible issues. Thanks, in advance! childofthe1980s Just off the cuff, are you using different IIS servers for this? Also, make sure you create your wss sites appropriately, I don't think this would be an issue, but ??? MIke "childofthe1980s" <childofthe1980s@discussions.microsoft.com> wrote in message ne...

How to get multiple lines on one graph
I have a data set that consists of pressure readings taken over time for two rooms. The readings range from -2.5 to +2.5. I want to develop a chart that has each rooms reading represented by a different line. The y axis would be pressure and the x axis would be time. I know, sounds simple. However, the times are not consistent across the data sets...the pressure readings were taken based on a change of value rather than a designated time interval. If anyone knows how I can do this I would greatly appreciate the assistance. I need to do this for approximately 50 different r...

how to create multiple labels on one sheet?
What I need to know is how to do custom size labels (about 1"x1") to be used as calendar save the date stickers with each 81/2x11 sheet having "sets" of approximately 10 different designs (logos inserted, etc.) that can be saved and printed. Are they stock labels? There are templates here: http://www.publishermvps.com/General/Templates/tabid/38/Default.aspx -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Gwyneth@MOD" <Gwyneth@MOD@discussions.microsoft.com> wrote in message news:4514112B-9FC9-4ED5...

One Form to enter/get data in two linked tables with 1 to many lin
Hello, I am a new user with a variant of a contacts database. Table 1 Hospitals, with one primary contact person info. Table 2 Volunteers in the hospital, can be many How would I go about designing one form which allows entry of the volunteer information, assigns an existing hospital or can create a new hospital with the volunteer information. On retrieving a volunteer's information, it must display the hospital/primary contact information. TIA, John On Thu, 25 Mar 2010 12:09:02 -0700, John L <JohnL@discussions.microsoft.com> wrote: >Hello, &g...

One link not working
I have a workbook that pulls info from the Payroll Master to calculate our paychecks. All those links work fine without having the Payroll Master open. However there is one cell that does a Countif and looks for "x", and returns the value. This link only works if the Payroll Master is open, when it's closed, it returns a error. Any ideas? just a thought, but maybe, is your worksheet set to 'Save External Lin Values'?? under Tools -> Options -> Calculation (tab) -> Save Externa Link Values (also Update Remote References) -- Message posted from http://www.ExcelF...

how do I set up an annual calendar on one worksheet
I need a tempplate to set up on one worksheet an annual calendar with days of the week, months and date numbers. I havae to set up a calendar that has recurring dates e.g. evry 3rd Weds in Feb, 4th Tues in may etc ANy help would be appreciated on all or some of the above start here: http://office.microsoft.com/en-us/results.aspx?Scope=TC&Query=calendar -- Gary''s Student "Ian Dee" wrote: > I need a tempplate to set up on one worksheet an annual calendar with days of > the week, months and date numbers. > I havae to set up a calendar that has recurring ...

Subtract
On sheet A, I have column: A1(product name) A2(quantity). When I copy a row with the data in above columns to sheet 2, it must automatically deduct 1(one) from A2. Can you please help? Thanks. --- Message posted from http://www.ExcelForum.com/ Hi this would require VBA (using event procedures) Do you really want to go this way. Note: excel is IMHO not well suited for maintaining an inventory -- Regards Frank Kabel Frankfurt, Germany > On sheet A, I have column: A1(product name) A2(quantity). > > When I copy a row with the data in above columns to sheet 2, it must > auto...

Subtracting figures
If i was to sell a product that i had bought and entered into Microsoft Money how do i tell money to minus the balance of what i sold it for? For example, i buy a phone for $50 and this goes under the Phone expenses category. Later on i decide to sell it and i manage to get $20 for it. How do i tell money to minus the $20 from the original $50 i paid? Thanks In microsoft.public.money, Stan wrote: >If i was to sell a product that i had bought and entered >into Microsoft Money how do i tell money to minus the >balance of what i sold it for? For example, i buy a phone >for ...

Can you Combine more than one excel spreadsheet onto one pivot tab
Is it possible to combine together more than one seperate excel spreadsheet onto one Pivot Table? ...

One-to-many connector
Hi, How can I draw a single connector starting from one shape and pointing to more than one shape? Now, I draw several connectors and overlap them, but it's getting annoying when changing the layout of the diagram. Thanks in advance. Raphael Try "Multi Tree Square" on the "Connectors" stencil under "Visio Extras" "Raphael Goubet" <rgoubet@yahoo.fr> wrote in message news:da59d264.0405050137.55c3b160@posting.google.com... > Hi, > > How can I draw a single connector starting from one shape and pointing > to more than one shape? No...