I have a workbook that I am using the NOW function to return today's date and then I have another cell to convert to WEEKNUM that I use to MATCH on with a different tab. I need to be able to create a formula to read today's date and always return the previous Saturday. Example: Today is 6-8-2015. I want this formula to return 6-6-2015. Any ideas on how to make this work. I am struggling here.

Answer:

How about: =NOW()-(WEEKDAY(NOW(),1))

This will return the time 'now' minus the number of days from Saturday where Sunday = 1, Monday = 2 etc.

Note, this does include the timestamp along with the day (eg. 6/6/2015 14:48). If you want just the date you could daisy-chain a text function around it.

excel,vba,excel-vba,excel-2007,excel-2013

I'm looking for a way to apply a color scale to a set of cells via VBA code but not by applying some conditional formatting... I want to apply them as static colors (InteriorColor) I've searched plenty of excel sites, google and stackoverflow and found nothing :( For my situation...

sql-server-2012,64bit,excel-2007,import-from-excel,windows-server-2012-r2

I'm running SQL Server 2012 on a 64bit Windows Server 2012 R2. I don't have MS Office installed. I now notice that when I start Start->Program Files->Microsoft SQL Server 2012->Import and Export Data (64-bit) the MS Excel file option is missing from the datasource dropdown. How can I get that...

excel,vba,excel-vba,excel-2010,excel-2007

I have a dataset in three columns composed of a repeating set of UUIDs in the first column, string responses for each UUID in the second, and a code for each response in the third. I need to break this out into multiple sets of columns, one for each repeating...

excel,vba,excel-formula,excel-2007

I'd like to know if a certain behaviour of my function in VBA is a bug or if there is something wrong with my code. I also would appreciate suggestions if it is a bug, how I could solve the problem. Option Explicit Public Function someFunction(dblWeight As Double) As Integer...

excel,date,if-statement,excel-2007

I want to write a function in Excel to change the date. The logic is like this: if the month is (Jan, Feb or March) the result show me one year past (-1 year) and if the month is (April to -December) the result show the current year (which year...

excel,excel-formula,excel-2010,excel-2007

I have two columns in Excel, formatted as General - with this data: Column A Column B 11/2/2014 9:12:27 AM 12/3/2014 2:00:00 AM How can I find the difference in hours between them, if they are formatted as "General" and not "Date"? Thank you for any advice, Mark...

excel,if-statement,excel-formula,excel-2007

I've been having a lot of trouble lately with this and haven't been able to find a solution yet. Here's my problem: I have two sheets: http://s4.postimg.org/aryzvl13v/Capture.png < image of the two tables I have to find the "sell price" in the first table. In order to do this I...

vba,excel-vba,excel-2007

I am trying to achive the values of a row holding specific text. Say Liquidity Coverage Ratio is the text that is available in B57. I need the value of C57 and D57. I have achieved until retreiving the address of the cell holding text. Kindly help me progressing further....

excel,vba,excel-vba,excel-2007,excel-2013

I'm trying to make VBA's GetTickCount work so that I can see the runtime of code, but it doesn't have to be super accurate. The following bit of code works good but I need a few changes and can't work out how to achieve this. #If Win64 Then Public Declare...

excel-vba,excel-2007

I need to Change Live Hyperlinks into Text URLs I have the following Code but after running it the Hyperlinks are still live I have Internet and networkpaths with hyperlink un-checked thanks Sub RemoveHyperLink() Dim ws As Worksheet Dim Rng As Range, Cell As Range Dim LC As Long, LR...

vba,excel-vba,excel-2007

My aim is to load the Worksheets(ws1...wsn) in the WorkbookA to WorkbookB with specific condition. I have formed an array of worksheets present in WorkbookA. Condition: I have a table(tblList) in WorkbookB which lists down the worksheet names that has to be ignored loading (Say I do not want to...

excel,excel-formula,excel-2010,excel-2007

I am trying to calculate 2 values. Current Streak and Long Streak. each record is on 1 row and contains a name and values each of those columns has a value from 1 to 200. Example: John Doe 14 16 25 18 40 65 101 85 14 19 18 9...

excel,vba,excel-vba,excel-2007,excel-addins

I have run into an issue in Excel 2007 which is very similar to a known problem of Excel 2003 described by Microsoft Support here ("You may receive a "Run-time error 1004" error message when you programmatically set a large array string to a range in Excel 2003"). The problem...

excel,vba,excel-vba,excel-2010,excel-2007

Step 1) Open Up Excel, Copy "stackoverflow's logo into sheet: http://blog.stackoverflow.com/wp-content/uploads/stackoverflow-logo-300.png Step 2) Copy and paste that logo twice Step 3) Resize one of the logos manually to some ridiculous size: http://i.imgur.com/87lIB8o.png Now how can that nasty logo be resized via vba to the same size as original logo I...

excel,excel-formula,excel-2007,highlighting,cells

Example of what I want. We have an excel spreadsheet. I want to 'group' i.e. E3, E6, E10 (NOT E4, E5, E7 etc... that's the point) cells so if I click of any of them, all of them will be highlighted. So let's say, I click on E3 so E3,...

vba,excel-vba,excel-2007

I have a worksheet with given data, I need to email the data using Micorosft outlook in the required format for a specific date(Say if the date is 05 Jan 2015).Refer below This is how the email should look, I am an amateur developer in formatting the email through VBA....

excel,vba,excel-vba,excel-2010,excel-2007

I'm trying to copy cells from one sheet to another without copying stuff to the clipboard but it must copy the formatting across. Here are the ways I've tried at the moment, any ideas how to accomplish my needs? LastRow = ThisWorkbook.Sheets("Nas").Range("A65536").End(xlUp).Row 'Option 1, works but it's using the clipboard...

excel-2007

I have a workbook that I am using the NOW function to return today's date and then I have another cell to convert to WEEKNUM that I use to MATCH on with a different tab. I need to be able to create a formula to read today's date and always...

excel-2007,transform,ms-query

I have 4 field in my table : NO. | Specification | Specification Value | Description 1 Color Blue a 1 Size 6" a 2 Color Red b 2 Size 8" b I am trying to Pivot it and its giving me an error. TRANSFORM FIRST(Specification Value) SELECT items.'No.' FROM...

excel-formula,excel-2007,formula

A B C 1 N1 4 3 2 N2 7 Blocked 3 N3 5 5 4 N4 12 0 I want to SUM the total of column "C" if it is numeric if it is less than column B I want it to be something like this, but obviously...

excel-2007

I need to return just the date portion of the following formula: =NOW()-(WEEKDAY(NOW(),1)). Any ideas? I am using this formula to get the current date and then return the previous Saturday's date, but I am also getting the time component and I need to drop that portion.

excel,excel-2007,aspose,aspose-cells

I create an Excel file (.xlsx) using the Aspose.Cells library. But I'm not able to read the data (retrieve rows) using OleDb commands after that, until I open the file and save it manually. I'm running something as simple as this one: new OleDbDataAdapter("select * from [Sheet1$]", conn); // etc......

ssis,excel-2007

Exporting from sql server to excel with ssis. export gets around 80% through writing to the excel file then fails with this error. It stops at the same row each time. The value in the field being written is the well within the length parameter and contains no funky characters....

excel,excel-vba,excel-formula,excel-2010,excel-2007

http://imgur.com/BYejKRb (please use the sceenshot linked above for reference - this is my first post so I can't post images) As you can see in the picture above, I want to take Table 1 and, using formulas that can auto-populate, turn it into Table 2. I tried using vlookup and...

excel,excel-formula,excel-2007,formula

What I'm looking for is a formula that will do the following: (I'll be using the first row for this example): Firstly check cells E:J for cells containing a 5. If all cells contain a 5 it will check the person's name and then add this to the box in...

excel-vba,excel-2007,pivot-table

For a pivot table (pt1) on Sheet1, I use VBA to change the value of a filter field (filterfield) using the code below. Let's say values for field can be A, B or C Sheets("Sheet1").PivotTables("pt1").PivotFields("filterfield").CurrentPage = "A" Occasionally, and let's say randomonly for the purposes of this question, A, B...

vb.net,excel-2007

Is it possible to write formulas across a range in Excel from VB.Net? I'm using a String array to hold a list of formulas that I would like to apply to an Excel range, instead of looping through and writing them one at a time. This line is what I...

vba,excel-vba,excel-2007

I have a code that will save the specified worksheets into a new workbook. The issue I'm having is it ignores a chart I would like to save. If I put "Figure 1" (the name of the chart) to the array list I get an error: "Subscript out of range"....

excel,vba,excel-vba,excel-2010,excel-2007

When I run my function, I get the following error: Run-Time error '424': Object Required And it highlights the following line of code: For Each n In DataSet Here is my full code: '==============================|Find_Occurrences FUNCTION|==============================' ' Given a collection and a search value, return the number of occurrences of the...

excel,excel-2010,excel-2007

I am trying to make lookup for excel table using the text in the columns For example if i have A1 | A2 BMW-X6 | 1 BMW-X5 | 1 Mazda-1 | 2 Mazda-2 | 2 Mazda-3 | 2 Mercedes-Benz | 3 Mercedes- cls | 3 I want where there is...

excel,excel-vba,excel-2007

I need to set a password for only one sheet in xls file, so when the file is open the sheet protected with password isn't visible, and the other sheets could be seen. I use MS Excel 2007, thank you....

excel,vba,excel-vba,excel-2007,excel-2013

I'm trying to transfer data from one document to a different document using "Copy Destination" as I want to avoid using the clipboard but I want it to stop taking the formatting with it... Dim Sheet As Worksheet Dim FoundLocationSheet As Boolean Dim n As Long Dim AllSheet As Worksheet...

excel,sorting,excel-formula,excel-2007

Essentially, I have the excel sheet like this - A B C D 16 14 13 12 14 14 13 14 16 14 15 12 14 14 15 14 I want the excel sheet to display this - A B C D 16 14 13 12 A B C D...

excel,excel-2007,filtering

I have imported a query into Excel, very straight-forward process. The issue I am having is if I click the header row and say filter, if I select any of the filter arrows, their are no values to filter on? What should I change so that I am able to...

excel,excel-formula,excel-2007

Sample data: Event Client 400 1 400 1 401 1 402 2 402 2 402 2 400 3 401 3 402 3 403 3 This table represents 1 ticket to an event per row. I am trying to determine how many events a client attended (but I don't care how...

excel,tabs,excel-2007

I have created an excel workbook with 11 tabs/sheets. The first sheet is a combined prioritization preceding the 10 individual department prioritizations each on their own sheet. I used the same column headers on all of the 11 sheets, with column A being "Division Priority". What i'm trying to do...

vba,excel-vba,excel-2007

I have an excel sheet A AAA 1 A AAA 2 A AAA 3 A ABC 1 A ABC 2 B AAA 1 B AAA 2 B AAA 3 B ABC 1 B ABC 2 I need it to look like A AAA 1 2 3 A ABC 1 2...

excel,excel-2007,conditional-formatting

I have a running sheet for my expenses. I would like to setup conditional formatting/Macro/ect. that looks in cells of THE CURRENT MONTH, then any cell that has the text "paid" FOR THE CURRENT MONTH it will apply a gray fill for THE ROW that the cell is in. EDIT:...

excel,vba,excel-vba,excel-2007

Sub testmac() ' ' testmac Macro ' ' ActiveWorkbook.Names.Add Name:="items", RefersToR1C1:= _ "=Report!R7C8:R486C11" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _ "ODBC;DSN=Excel Files;DBQ=path ), Array("P;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;")), Destination:= _ Range("$A$1")).QueryTable .CommandText = Array( _ "TRANSFORM first(`Dimension Value Code`)" & Chr(13) & "" & Chr(10) & "SELECT items.`No#`, items.`Description`" & Chr(13) & "" & Chr(10) &...

excel,excel-formula,excel-2010,excel-2007

I have simple problem. I was analyzing some data and came up with this problem. Below is my value in a colomn: www.mysite.come/api/Customer?id=12333&name=jack www.mysite.come/api/Department?id=52365&name=COP www.mysite.come/api/Customer?id=13333&name=mathew etc I want to filter this data something like this www.mysite.come/api/Customer www.mysite.come/api/Department www.mysite.come/api/Customer Please help me with this....

r,loops,merge,excel-2007,lapply

I have many excel files that I need to load and merge into a single data frame. The script below works! However, before merging all files I want to paste each file name in a new column. library(gdata) library(tools) filelist <- list.files(pattern = "*\\.xlsx$") files = lapply(filelist, read.xls, header=TRUE) new...

excel,vba,excel-vba,excel-2007

I was looking to create a program that examined a column of text in excel and extracted the first line that contained currency. The currency was in Canadian dollars and is formatted in "C $##.##" with no known upper bound but unlikely to reach 10,000 dollars. I was hoping to...

excel,excel-formula,excel-2007

I use filters a lot. Filter > contains option But it is slow because everytime i need to do several clicks. So, my question is, is there a shortcut to open filter, drop down, contains window?...

excel,excel-vba,excel-formula,excel-2007

To explain it in the easiest way possible: | 1 | 2 | 3 | 4 | | 5 | 6 | 7 | 8 | ... needs to look like: | 1 | 2 | | 3 | 4 | | 5 | 6 | | 7 | 8...

excel,vba,variables,excel-2007

I need to call a value of a variable as another variable. E.g. I assign the FirstVariable = "One" and then I asssign the Name as Text to SecondVaribale = "FirstVariable" (Note here it is the "TEXT") So now can I call or assign the SecondVariable to return the value...

excel-formula,excel-2007

I am trying to write a conditional formatting formula for the rule "if the first cell of this row is NOT empty AND the current cell IS empty". Basically I am trying to bring attention when a user has forgotten to enter a value in a cell. The formula I...

excel,vba,excel-vba,excel-2007

I have a worksheet function with list objects on it and it does not give the proper value. The output is always 0. Sub volsumif() Application.ScreenUpdating = False Set wb2 = ThisWorkbook Set ws1 = wb2.Sheets("Dist_Summary") 'Set a = wb2.Sheets("SALES").ListObjects("SALES").Range Set ws2 = wb2.Sheets("Dist_Perf Total Brands") Set ws3 = wb2.Sheets("SALES")...

excel,excel-vba,excel-formula,excel-2010,excel-2007

Hello I'm trying to delete all the rows where in column B the members value is over 1000. I tried this step by step and tried first getting rid of all the unecessary data from B cells and leave just the line with the members. I noticed there are 5...