Computer Support Forum

Excel Macro working with dates

Question: Excel Macro working with dates

Hello,I am trying to write a macro to determine the percentage of dates in a column of cells (format: 30-Sep-10) that occur before the end of the current calendar year.I have tried using the DCOUNTIF function, but I think I am having problems with excel recognizing the date and working with the date in the context of the current calendar year. This is what I have, which doesn't seem to work at all. The C[-2] is the column with the dates in it:For i = 2 To Sheets.Count Worksheets(i).SelectRange("J8").Select ActiveCell.FormulaR1C1 = "=DCOUNTIF(C[-2]:C[-2],<DATE(year(now())+1,0)" Range("K8").Select ActiveCell.FormulaR1C1 = "=DCOUNT(C[-3]:C[-3])" Range("L8").Select ActiveCell.FormulaR1C1 = "=((RC[-1]-RC[-2])/RC[-1])" Range("J6").Select ActiveCell.FormulaR1C1 = "Ground Task IDs" Range("J7").Select ActiveCell.FormulaR1C1 = "due this year" Range("K7").Select ActiveCell.FormulaR1C1 = "Req" Range("L7").Select ActiveCell.FormulaR1C1 = "% complete"NextThank you for any help.

Relevance 100%
Preferred Solution: Excel Macro working with dates

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

Answer: Excel Macro working with dates

Hi,I don't think that you need a macro to do this.As a test I had 1000 dates in column A in cells A2 to A1001The dates ranged from 01 January 2009 to 31 December 2011The method I used is based on counting the total number of dates and the total number of dates that are on or before 31 December 2010.Then calculate the percentage.=COUNT(A2:A1001) counts all cells containing numbers (Excel stores dates as numbers - 40373 is 14 July 2010)I used =COUNTIF(A2:A1001,"<=" & DATE(2010,12,31)) to count the number of cells containing a date on or before the 31st December this year.Putting all this into one formula I get:=COUNTIF(A2:A1001,"<=" & DATE(2010,12,31))/COUNT(A2:A1001)Then format the cell with a percentage number format.Or if you prefer the value directly use:=(COUNTIF(A2:A1001,"<=" & DATE(2010,12,31))*100)/COUNT(A2:A1001)Regarding a macro, i did this:Option Explicit

Sub datePercnt()
Dim intTotal As Integer
Dim sngBefore As Single
Dim rngDates As Range
Dim dtEndDate As Date
Dim rngCell As Range
Dim sngResult As Single

'set the range containing dates
Set rngDates = Worksheets("Sheet1").Range("A2:A1001")

'set counters to zero
intTotal = 0
sngBefore = 0

'set end date
dtEndDate = DateSerial(2010, 12, 31)

'loop through each cell in the range
For Each rngCell In rngDates
'add 1 to count of all dates
intTotal = intTotal + 1
'test date in cell against end date
If rngCell.Value <= dtEndDate Then
'if on or before add 1 to 'before total'
sngBefore = sngBefore + 1
End If
Next rngCell

'calculate percentage
sngResult = sngBefore * 100 / intTotal

'display result
MsgBox "The percentage of dates in the range:" & vbCrLf _
& CStr(rngDates.Resize(1, 1).Address) & " to " _
& CStr(rngDates.Offset(rngDates.Rows.Count - 1, rngDates.Columns.Count - 1) _
.Resize(1, 1).Address) _
& vbCrLf & "that were on or before: " & Format(dtEndDate, "dd-mmm-yyyy") _
& vbCrLf & " was: " & Format(CStr(sngResult), "#0.00") & "%"

End SubNotes1. In Visual Basic macros it is typically not necessary to select a cell before doing something with it.2. Looping through a range of cells (or a range of objects) can be done with the for each ... next structure. Excel takes care of the counting. You can loop through a collection of worksheets in a workbook, without having to know how many there are - Excel keeps track for you.3. As dates are stored as numbers, most times you can do comparisons, differences etc with simple math functions (but remember that Excel also stores times in the same number (as the decimal part) so sometimes 31 December 2010 is not equal to 31 December 2010 if the times are different. To be sure, you can use the integer or whole number part of the values so that just the date is compared.4. I try and use the A1 notation for cells as I find it easier to check what I have against the actual worksheet.5. Use Option explicit - it helps reduce errors due to typos. It shouldn't let you do sngBefore = sgnBefore +1 and you must DIM everything before you first use it - that also helps with Intellisense that prompts you with what is available, such as typing rngCell then the dot and you get a list of what can come nextRegards

8 more replies
Relevance 80.36%

=IF(15-(LEFT(TEXT(AN2,"DDMMYYYY"),2))>=0,CONCATENATE(15,RIGHT(TEXT(AN2,"DD-MM-YYYY"),8)),CONCATENATE(30,RIGHT(TEXT(AN2,"DD-MM-YYYY"),8))) ----- this was my excel formula, its a macro created with word link, my question was in this formula only I need change that the month end 30 because the macro created like this, because according to month end its 30 / 31 and 28 every time we have to change the month end, in 30 there is no need to change but in 31 and 28 we have to change mannually in word. So this I want to change Can u help me in this

Answer:Excel and word macro about month end dates

Don't forget Leap Year February has 29.

3 more replies
Relevance 70.11%

I am having problems working on a spreadsheet of dates and am looking for help. I am creating a file to show membership ages and senority in our area. Here is my setup.The first column: "Members name". Second column: "Date of birth". Third column: DOB converted to automatically updated "Age" by Year and Month with "Datedif". Fourth column: "Hire date". Fifth column: Hire date automatically converted to "Length of Senority" by Year and Month with "datedif".I want to get the totals and averages of the Age and Senority columns. I think I have the averages worked out by using =AVERAGE(B2:B49) and then converting that with =DATEDIF(B53,TODAY(),"Y")&" Yr, "&DATEDIF(B53,TODAY(),"YM")&" M". But I am not having any luck with the totals.Can anyone help?

Answer:Working with dates in Excel

It is rather difficult to visualise this.Could you upload the file (with the members names removed) to a site like click here and provide a link to download it.

6 more replies
Relevance 70.11%

Hi there

I'm having problems with dates in a macro. To simplify things, here is an example of my problem. I have code that goes something like this:

Range("K1").Select
Do
If Range("k1") <= Range("D9") then
ActiveCell.Offset(0, -1).Range("A1") .select
End If
Loop Until ActiveCell >=Range("K1") or ActiveCell = ActiveCell.Text

Cells K1 and Cells D9, C9 and B9 all have dates. Assume that the dates are as follows:
K1 = Sept 05, 2005
D9 = Oct 15, 2005
C9 = Sept 05, 2005
B9 = Aug 2, 2005
A9 = text

The problem is that the cursor stops at the text cell (A9) and completely by-passes all the dates. It should stop at Cell C9. If C9 did not equal K1, then the cursor should stop at cell D9.

The macro works fine if I take out the inequality signs and just leave the equal signs. What Am I doing wrong??

Please help.

Mario
 

Answer:working with dates in Excel

7 more replies
Relevance 70.11%
Relevance 69.7%

i am trying to make a spreadsheet that will tell me when a customer has not made a payment on an item in X amout of months. ex: i want the cell to turn red if it has been over 3 monoths since the customer has made a payent. basicly it is just conditional formatting on the cells. i don't know and can't figure out how to work with dates in the formula bar, especially display the current date

help would be much appreciated, TY
 

Answer:Solved: working with dates in excel

Hi there,

Dates are arranged by Serial Numbers. One whole number is one whole 24 hour period, so one day is 1, two days is 2, thus 0.5 works to be half of a day.

The formula for today's date is =TODAY()

So in your conditional formatting, you could use something like this ...
Code:
=A1<TODAY()-90
This assumes that A1 is the cell you are in and the one with the date in question in it.

Post back if you need more help.

HTH
 

2 more replies
Relevance 67.24%

Hi,I am using a SQL query to import data into Excel. The database has some time fields which are formatted as datetime rather than just time, so each entry is in the format yyyy-mm-dd hh:mm.In all cases, the time is less than 24 hours (this is the time recordal program for our consultants) per entry, so the date element is always the same. For some reason, the date is always shown as 1899-01-01 in the database, and this has never been an issue when using Excel 2003 - the date simply displayed as 00-01-1900 and then the relevant time was shown. For example, if some had spent 1 hour 30 minutes on a job, the relevant entry, when imported to Excel, would be 00-01-1900 01:30.However, since 'upgrading' to Excel 2010, the above example now returns 00-01-1900 00:00, and switching the format of the cell to Number or General shows the value as being 0.This used to work fine in Excel 2003, so any ideas on what has changed in Excel 2010?Thanks.

Answer:Imported dates earlier than 1900 not working in Excel 2010

This may help: http://www.exceluser.com/explore/ea...

3 more replies
Relevance 66.42%

I have no idea if this is even possible, but I would like to create a spreadsheet that auto-populates a series of dates based on one date that the user enters. Let's say I have a goal date to complete a project, and for every project I work on, something needs to happen 6 weeks prior to the goal date, another thing needs to happen 4 weeks prior to the goal date, and something else needs to happen 1 week and 5 days before the goal date. I would like to set up 4 cells in Excel: one for the goal date, and three for the various things that need to happen leading up to that goal date. I would like to be able to enter the goal date into the main cell and have the other three cells auto-populate with the correct date based on what I enter in the goal date. For example, if I enter a goal date of 11/01/2016, I would like the other three cells to automatically populate themselves with: 09/20/2016 (6 weeks prior), 10/04/2016 (4 weeks prior), and 10/20/2016 (1 week and 5 days prior). Is this possible? Can Excel return dates in that way?

Answer:How can I auto-populate dates in Excel based on other dates?

Sure, it's easy. Excel internally stores Dates and Times as numbers. Each day is a whole number and time is the decimal portion of that day. See here for a pretty good explanation of how Excel deals with Dates and Times as well as links to all the various Date and Time functions that Excel offers:http://www.cpearson.com/excel/datet...As for your question, simply subtract the number of Days required from the cell with the Goal Date.With 11/01/2016 in A1, use this to return 9/20/2016:=A1-42(42 is 6*7, or 6 weeks.)I'll leave the other two formulas to you. ;-)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 64.37%

I have been using a macro in my excel spread sheet for a year. it usually updates the formula to the next row cells but now it is just adding the rows and not the formula. How would I repair this if I didnt create the macro or why has it stopped working

Answer:excel macro is no longer working

First thing that comes to mind is to make sure you still have formulas in the row to be copied.How big is your spread sheet?I haven't tested the macro extensively, but it should keep adding rows till you hit the bottom of the sheet at row number 1,048,576 MIKEhttp://www.skeptic.com/

13 more replies
Relevance 63.55%

How do i get the same macro format but for it to read from Sheet2 CX2:CX150 And only give the answer for the row that they are on in Sheet 1, eg, AT3 would run macro and show CX3

I have already worked out how to get the macro working, if someone clicks Yes in the listbox in Sheet 1, AT2.. it then runs the Worksheet_Calculate2 macro and checks to see if any of the cells are not correctly filled in (in this case, CX2 checks multiple cells) however when they goto AT3, and then click yes, then i'd need another macro for the excate same thing but for CX3..



Code:

Sub Worksheet_Calculate2()
Application.ScreenUpdating = False
If Range("Sheet2!CX2").Value > 0 Then msgbox "Theres At least 1 Cell Not Filled in, Please check again and then continue"
End Sub


This is a stubbon problem and i've been trying to work on it for nearly a week now..
If i can just get some help on how to do this...

Thanks in advance..
 

Answer:Problem with macro in Excel VBA working across sheets

Hi Bitsbb01,

If you are using worksheet level event then you can use offset, something like

Private Sub Worksheet_Change(ByVal Target As Range)
If VBA.InStr(1, Target.Address, "AT", vbTextCompare) Then
Worksheet_Calculate2 Target
End If
End Sub

Sub Worksheet_Calculate2(ByVal SelectedCell As Range)
Application.ScreenUpdating = False
If SelectedCell.Offset(0, 56).Value > 0 Then MsgBox "Theres At least 1 Cell Not Filled in, Please check again and then continue"
End Sub
 

3 more replies
Relevance 63.55%

Hi, I was coding in VBA and it runs fine there but when I run the same function using a macro (which was working before), it will not do anything. I check that macros are enabled. Does anyone have any ideas as to why the macro button would not work?Hi, I was coding in VBA and it runs fine from there but when I do the same by clicking the macro button (which was working before), it will not do anything. I checked that macros are enabled. Does anyone have any ideas as to why the macro button would not work? Thank you.
- Jeff
 

More replies
Relevance 63.55%

Hi.

I have built a macro that imports Data from another worksheet, and to add to the user experience, I want to add a progress bar.

I have added the code, and userform to make this happen, however, it wont update (i.e. show me the progress).

I have tried turning screenupdating on, setting the userforms properties ShowModal=True, and ShowModal=False.

When the ShowModal is set to true, the user form actually displays, but gets itself into a neverending loop and wont quit.
When the ShowModal is set to false, all I get is an outline of the form, but the middle of the box is white and works as normal.

The code is invoked from several different subs as I have different subs that import a different part of the worksheet.

I am using the following code:

Progress.Show
Progress.lblBreakoutNo.Caption = "Currently Importing"
PctDone = BreakoutNo / NoofClaims 'update Progress bar
With Progress
.FrameProgress.Caption = Format(PctDone, "0%")
.LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
End With
Any help would be appreciated.

Thanks

Lightingman2003
 

Answer:Solved: Excel Macro - Progress Bar - Not Working

I have now managed to solve it. It was user error, forgot to add "DoEvents" after the ProgressBar.

Hope this helps somebody else out though.

Will now mark as "Solved".

Thanks

Lightingman2003
 

1 more replies
Relevance 63.55%

My clients have a macro that automatically generates an email .. however after our recent upgrade to Office 2003 the macro is producing an error on the send mail portion.

Here is the code:

Sub Send_Email()

Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim cell As Range
Dim Email1 As String
Dim Email2 As String
Dim Email3 As String
Dim Email4 As String
Dim Email5 As String
Email1 = Range("emailto1")
Email2 = Range("emailto2")
Email3 = Range("emailto3")
Email4 = Range("emailto4")
Email5 = Range("emailto5")
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = Email1 & ";" & Email2 & ";" & Email3 & ";" & Email4 & ";" & Email5
.Subject = "Deal List Update"
.Body = "A transaction requiring special approvals has been entered in the deal list." & _
vbCrLf & vbCrLf & "Trade Date: " & Range("trade_date") & vbCrLf & "Counterparty: " _
& Range("counterparty") & vbCrLf & "Deal Description: " & Range("description")
.Send
End With
Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")
Set OutMail = Nothing
cleanup:
Set OutApp = Nothing
End Sub

I am hoping it is a simple thing ... really appreciate any assistance.
&n... Read more

Answer:Excel macro not working after upgrade to 2003

Hi there, welcome to the board!

A couple reasons which may be of issue to you:

1) You will probably need another reference to Outlook 11.0 Object Model (version 2000 was 9.0), or use Late Binding (you are using Early)
2) Your specified ranges are not referenced via worksheet or workbook

So, update the workbook and worksheet in this code and see if it works for you...
Code:
Sub Send_Email()

Dim OutApp As Object ' Outlook.Application
Dim OutMail As Object ' Outlook.MailItem
Dim wb As Workbook, ws As Worksheet
Dim Email1$, Email2$, Email3$, Email4$, Email5$
'// Alter these lines to suit...
Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet1")
'//
Email1 = Range("emailto1")
Email2 = Range("emailto2")
Email3 = Range("emailto3")
Email4 = Range("emailto4")
Email5 = Range("emailto5")
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0) '(olMailItem)
With OutMail
.To = Email1 & ";" & Email2 & ";" & Email3 & ";" & Email4 & ";" & Email5
.Subject = "Deal List Update"
.Body = "A transaction requiring special approvals has been entered in the deal list." & _
vbCrLf & vbCrLf & "Trade Date: " & ws.Range("trade_date") & vbCrLf & "... Read more

2 more replies
Relevance 63.55%

Hi All

I have a trial excel 2007 version that came with my Vista Home Premium.
I had a spreadsheet which contains macros and I had no problems running with XP.

I changed some of the settings and completed the following:
http://www.howtogeek.com/howto/wind...nt-control-uac-the-easy-way-on-windows-vista/

I followed the following link from the developer of my particular spreadsheet and completed the following for 2007 version:
http://www.mdmproofing.com/iym/macros.php#excel2007

I still cannot use some of the functions that I had been using before such as Sort and doubling clicking a cell which is supposed to open a calendar and auto function add monetary figures.
Could it be the trial version? Any help is appreciated.

Thanks,
Transformer Man
 

Answer:Solved: Excel Macro Vista not working

Hi Transformer Man,

I was just wondering, have you solved the problem for Macros working on your Vista Machine?

As I currently working with a collegue to solve this problem, and would like to confer with someone else who has had a similar problem.

Regards,
Tom Griffiths.
 

3 more replies
Relevance 62.73%

Hi

I started to use VBA recently. I have a macro, which use solver to get some solution. The worksheet contains about 14,000 rows of data and I need to use solver as many times. In the macro, I added Application.ScreenUpdating=False at the beginning and Application.ScreenUpdating=True at the end. But it seems to me that the screen kept updating. I can see the cells changing values one by one. It took more than one and a half hour to finish the macro. I guess the Application.ScreenUpdating=False doesn't really work in my macro. But I couldn't figure out the reason. Any help or suggestions are greatly appreciated.

Since my data is pretty large, I created a "vbtry.xls' and write the macro in a simple manner. But it uses the solver repeatedly. The macro name is "test". As you can see, the screen keeps flashing if you run the macro. My understanding is that: with Application.ScreenUpdating=False, the window should look like freezing and the data should be posted all at once at the end. Am I right on this? What's wrong with my macro?

Please see the attached file. Thanks.
 

Answer:Solved: EXCEL: Is ScreenUpdating=False working im my macro?

In your example you have not defined SolverSolve, Solverfinish, and SolverOK. When I run the macro the code bugs out. Are you missing some code? Explain how the macro is supposed to work and what these variables or procedures are. Is there an add-in missing from the project?

Regards,
Rollin
 

3 more replies
Relevance 62.73%

Hi there

John Walkenbach, the Excel guru who wrote many Excel VBA books, gave me this hyperlink macro that finds Excel workbooks. The macro is complex but I'm sure that someone out there can figure it out. This macro works great in Excel 2000 but does not work in Excel 2013. When I run the macro in Excel 2013 I get a message that says "File not found" and I can't figure out why. I believe that the line "FileType = msoFileTypeExcelWorkbooks" could be causing the problem but I'm not sure.

Thanks

Mario

Below is the code.

Sub Hyperlink1()

Dim i As Long
Dim Folder As String
Dim FileToFind As String

Set objShell = CreateObject("Shell.Application")
On Error Resume Next

Folder = "C:\"
FileToFind = ActiveCell
Select Case ActiveCell
Case ActiveCell
Case Else
MsgBox "The file was not found"
End Select

With Application.FileSearch
.NewSearch
.LookIn = Folder
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 1 Then
If .FoundFiles.Count = 0 Then
MsgBox "File not found.", vbCritical

Else
For i = 1 To .FoundFiles.Count
If Right(.FoundFiles(i), Len(FileToFind)) = FileToFind Then

Workbooks.Open .FoundFiles(i)
End If
Next i
End If
End If
End With
Set objShell = Nothing
End Sub
 

Answer:Solved: Hyperlink Macro not working in Excel 1013

Apparently MS has degrade the FileSearch over time.

This looks like something to try Microsoft.com Thread
 

14 more replies
Relevance 62.32%

First time poster so bare with me.

My issue is: My customised QAT in Excel 2007, that includes 'not in ribbon' buttons, doesn't save when I add a macro button for default or specific docs.



The macro button and the macro will work fine when I add it, but when I close and reopen the file or email the file, the button has gone

The bizarre thing is, it was working a few weeks ago. I even had some customised macro buttons in the file that I was trying to add more to, and when I saved and reopened the file, ALL the custom macro buttons disappeared.

I've already searched google for days now trying to find a solution.....

I've read ron debruin's site back to front http://www.rondebruin.nl/qat.htm and http://www.rondebruin.nl/qat2.htm

The instructions are there for the process but no solution as to why the button wont stay put.

I'm running Windows 7, and obviously Office 2007. Also, this is a work PC on a network (I'm not the network admin). The network admin hasn't done anything to the system that he thinks would have done the changes i'm whinging about.

Any help, ANY!!!!, will be greatly appreciated.

Thanks

D
 

Answer:Excel 2007: Macro Quick Access Toolbar Not Working

WoW

I've either not explained myself properly or i've stumped even the brightest of PCReview forumers.

Guess i'll have to live with the issue

Thanks anyway
 

13 more replies
Relevance 61.5%

I need a macro that will enter the dates on the tab like this... Wed June 01, etc. And I need it to keep going till the end of the year. HELP

Answer:macro to enter dates on tab

In a new workbook with 3 sheets, this code will delete sheets 2 & 3 and then add and name sheets starting with Wed June 01 and ending with Sat December 31.Sub DailySheets()
'Turn off alerts so that sheets can be deleted without warning
Application.DisplayAlerts = False
'Delete Sheet 2 and 3
Sheets(Array("Sheet2", "Sheet3")).Delete
'Set Start date as 5/31/2011
nxtDay = DateSerial(2011, 5, 31)
'Increment date, rename sheet, add new sheet
Do Until nxtDay = DateSerial(2011, 12, 31)
nxtDay = nxtDay + 1
nxtSht = nxtSht + 1
Sheets(nxtSht).Name = _
WeekdayName(Weekday(nxtDay), True) & " " & _
MonthName(Month(nxtDay)) & " " & _
WorksheetFunction.Text(Day(nxtDay), "00")
ActiveWorkbook.Sheets.Add after:=Sheets(Sheets.Count)
Loop
'Delete extra sheet
Sheets(Sheets.Count).Delete
'Turn alerts back on
Application.DisplayAlerts = True
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

6 more replies
Relevance 60.27%

Hello everybody,I have 10 excel workbooks each with numerous worksheets within each, labeled 'stock 1' to 'stock 45' for example. Now within each worksheet, i would like to delete the rows which contain specific dates around christmas (ie. 24/12/##, 25/12/##, and 26/12/##). The dates start in column A row 3.Does anyone out there now now to do this quickly via a macro of some sort, as doing it manually would take about six months i reckon!Thanks very much.

Answer:Delete rows with specific dates using macro?

Try this in a backup copy of your workbook. Since it deletes rows, and can't be undone, test it in a backup copy first.Sub NoMoreChristmas()
For shtNum = 1 To Sheets.Count
With Sheets(shtNum)
lastRow = .Range("A" & Rows.Count).End(xlUp).Row
For numRow = lastRow To 3 Step -1
If Month(.Range("A" & numRow)) = 12 Then
If Day(.Range("A" & numRow)) > 23 And _
Day(.Range("A" & numRow)) < 27 Then
.Range("A" & numRow).EntireRow.Delete
End If
End If
Next
End With
Next
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 60.27%

I find that macros work great for certain queries and automating emailing those queries.

But I'd like to have them automatically run if possible (monthly), is that something anyone has done before?

I haven't seen anything in the Macro design that does this, but I might just be overlooking it.

Thanks
 

Answer:Access macro - automatic monthly run dates?

This should be possible in VBA rather than a macro, but anything you do will rely on the Database being "open".
 

2 more replies
Relevance 59.04%

I am using the MSN Stock Quote addin with MS Excel 2003. Prior knowledge of this should not be necessary, but I just thought I'd provide full context to my issue. I wrote a macro that essentially records daily stock prices. Because I don't want to store old quotes into perpetuity, i need to ensure that the update quotes button has been hit. I use sendkey() to accomplish this, and then the macro copies the quotes and pastes them elsewhere. My main goal is to have the task scheduler open this file daily so i dont ever have to think about this again, and i'm so close. Essentially my problem is that it takes a few seconds after the update button is hit for the add-in to fetch the new prices from the internet, and this process appears to be delayed until the macro is finished. This is a problem because the macro ends up recording un-updated quotes. I have tried the Application.Wait procedure, and I've tried time consuming loops, do while loops, etc. All of these approaches result in the same thing: Can someone please help me with this? I'm too lazy to hand update this spreadsheet every trading day for the rest of my life (or until i quit). Thank you in advance!
 

Answer:Excel Macro - Allow cells to update query before macro completes

Can't you just put the update macro code in a module of it's own and call it from the main macro?

Regards,
Rollin
 

1 more replies
Relevance 58.22%

I am trying to create an AutoOpen VBA Macro in Word 2010 files for automatically inserting the same dates into 2 different text boxes when I first open the file. Ideally, I would want 1 file to do this always for Yesterday's date (whatever that may be) and another file to do this for Friday's date (whatever that may be).

Reason for this is because our mail room gets mail the day before I get it on my desk and I need to date stamp it using a Word 2010 template where I have 2 text boxes (please see attached file--oh, doesn't seem like I can attach the file???) for Tuesday through Friday. Right now, I am using the Macros I have created (1 for Yesterday's Date and another Macro for Friday's Date which is Date() -3).

I want to use that Macro logic to put that into the Text boxes I have when it first opens? Please help me with this anyone......

It doesn't seem like I can attach the file so here is the VBA code:

Sub AutoOpen()
'
' AutoOpen Macro that places the cursor at the last position of edit (not default of top left) (will work with Protected View)
If Application.ActiveProtectedViewWindow Is Nothing Then
Application.GoBack
End If

' InsertTextBox1 Macro
Dim Shp As Shape
Set Shp = ActiveDocument.Shapes.AddTextbox( _
Orientation:=msoTextOrientationVertical, _
Left:=22, Top:=252, Width:=25, Height:=170)
Shp.TextFrame.TextRange.Text = "NMM RECEIVED: "
Set Shp = Nothing
Selection.InsertAfter Format(Now() - 1, "dddd, ... Read more

Answer:Word 2010 VBA AutoOpen Macro to Insert Dates Into Text Boxes

16 more replies
Relevance 57.4%

For some reason as I have added dates to cells, they are not getting converted to the Date type I have selected: 15/03/2009I type in my entries in this format:March 15, 2009 (usually it converts immediately but not this time)I've tried selecting the columns of cells with dates, Select>Format>Cells>Date and Type but nothing changed (although one date was converted to 03-15-2104Any suggestions?

Answer:converting excel dates in Excel 2003

Dat's cuz they're probably not being recognized as dates by Excel. My guess from afar is that the cells are formatted as Text and aren't accepting any other formats. Excel is finicky like that sometimes.Try this with one of your stubborn "dates"...let's assume it's in A1=DATEVALUE(A1)Now format that cell anyway you want.Then do a Copy...PasteSpecial...Values to eliminate the formula.

7 more replies
Relevance 55.35%

Hi, I'm new to this forum (actually forums in general) I was wondering if anyone could help me with a macro code. I work with 4 Excel sheets for my shippments, 2 sets for one plant and 2 for the other but all within the same file.

What I am trying to do is set up a macro that will automatically search for changes within sheet 1 (also for sheet 3, the other plant) When I change the rows color from blank to green/red, I need the macro to automatically cut that whole row and paste it into sheet 2 on the next blank row.

This doesn't seem like it would be that hard to do, but I know know the whole macro language.

If you can answer this please send me an email to soulblazor(@)hotmail.com

Thank you for your help.

More replies
Relevance 54.94%

Hi,

I was wondering if anyone could help me. I need to extract data from multiple excel files into a new excel file and sum the values. The data comes from excel files that are made from a template so the data is always in the same place or same cells. For example, i have data saved daily in excel files with the following naming convention 080204, 080304, 080404,..... and so on. What i would like to do is have a macro that will create a week ending sheet by importing and summing the data from that week.

Thanks for reading!
 

Answer:excel macro for copying and pasting data from multiple excel files

thanks for all those who read!!!! let me know if anyone needs the code!
 

1 more replies
Relevance 54.94%

Hello again,

I have a word doc that has a form in it. The form may have any number of fields (boxes) to type text answers into, or be a drop down list to select an answer from. I need an excel macro that extracts all data from the form, but with every five fields starting a new row in excel. So all data from the form will appear within 5 columns in an excel spreadsheet.

field 1 field 2 field 3 field 4 field 5
field 6 field 7 field 8 field 9 field 10
etc

Is that possible? I have had no luck to do it myself.

Sample form attached, thank you
____________________________
 

Answer:Solved: Excel macro to extract Word form data into Excel

7 more replies
Relevance 54.94%

Iím using Windows 7 and I used a macro which worked totally fine under Excel 2003. Now, under Excel 2007 it does not run anymore but displays the following error message:

"Run-time error '445':
Object doesn't support this action.

The function of the macro is to open up all other Excel spreadsheets located in the same folder and copy data out of them into the spreadsheet in which the macro is stored.

When I click on "Debug" the Visual Basic Editor highlights the row which says: Set FilSrch = Application.FileSearch

Can you help me with this? I've searched for a solution for a long time but could not find anything which worked.

The following is the start of the macro code:

Sub UpdateTable()
Dim X As Range, I As Integer, J As Integer, RecNo As Integer, CopyVal As Variant
Dim SourceBk As Worksheet, DestBk As Worksheet, StartRow As Integer, IndRange As Range
Dim ServRange As Range, SrcOpen As Boolean, SourceName As String
Dim FilSrch As Object, MyFilArray() As String

'MsgBox "This function not available in this version.", vbInformation, "Function Not Available"
'Exit Sub
'Sheets("LookUp").[a12] = Now
With Application
.ScreenUpdating = False
.StatusBar = "Counting source files ..."
End With
Set FilSrch = Application.FileSearch
With FilSrch
.NewSearch
.LookIn = ActiveWorkbook.Path
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
ReDim MyFilArray(.FoundFiles.Count)
For I = 1 ... Read more

Answer:Solved: Excel 2003 Macro Doesn't Run in Excel 2007 - Help Requested

7 more replies
Relevance 54.94%

I am trying to create a code that will autofilter data on an Excel worksheet. The code works fine to filter the data, but it does not hide the AutoFilter Arrows.

Below is the code I am using. What (if anything) is wrong with the code below?
Code:

ActiveSheet.Range("A1:D500").AutoFilter Field:=2, Criteria1:="<>", Visibledropdown:=False
 

Answer:Solved: Excel 2007 Macro -> Hide Excel AutoFilter Arrows

I noticed that the code above (in Post #1) that the AutoFilter Arrows were beinf truned off on Field 2, but none of the others.

I have played around, and done some research to come up with the code below:
Code:

Dim c As Range

For Each c In ActiveSheet.Range("A1:D1")
ActiveSheet.Range("A1:D500").AutoFilter Field:=2, Criteria1:="<>"
c.AutoFilter Field:=c.Column, Visibledropdown:=False
Next

Does anyone have a better solution?
 

1 more replies
Relevance 54.94%

Hello,

I need help to create an Excel macro that would

1. ask users to select x number of rows to be copied from one worksheet of one Excel file
2. once users have selected the rows to be copied, then the rows are copied from the one original Excel file into X number of target Excel files

Notes:
the target Excel files are all based on the same template
there is one worksheet in each of the target Excel files

in the target files, the rows should be copied from the first available empty row, going down

Looking forward to your help!

Thanks a lot.
Mzz
 

Answer:Macro needed to copy x number of rows from one Excel file into a batch of Excel files

Hi, welcome to the board.
Not much info there to get the correct picture.
Sample? Of source and template please
And not to forget, what version of Excel are you and the users using?
 

1 more replies
Relevance 54.53%

I'm working on a Bill of Material creation automation project that requires some expertise in VBA, and I have none.
The objective is to run a macro from an excel spreadsheet called "PGE BOM", to do the following:
1) Go to the folder "C:\Documents and Settings\Desktop\Auto Project"
2) Find all the .mdb databases in this folder
3) Find "HistoricalMaterialItemsAll" table in EACH of those databases in step 2, and import the data from the columns listed below into PGE BOM.xls's columns C through G:
DrawingNumber
ItemNumber
Quantity
PgeCode
Description
The following is a VBA code that my friend had written in Excel 2007. Unfortunately I have an older version (2000) and the code does not seem to be compatible with Excel 2000.
Sub ImportAccessData()
Stop
dPath = "C:\Documents and Settings\Desktop\Auto Project\"
sFile = "*.MDB"
strSrch = dPath & sFile
Set TargetWB = Application.ActiveWorkbook
Set TargetWS = TargetWB.ActiveSheet
sRow = 2
bFile = False
If Dir(strSrch) <> "" Then
strFlNm = Dir(strSrch)
bFile = True
End If
Do Until bFile = False
strPath = dPath & strFlNm
Call GetData(strPath)
strFlNm = Dir
If strFlNm = "" Then bFile = False
Loop
End Sub
Sub GetData(fl)
Stop
strSQL = "Select HistoricalMaterialItemsAll.* From HistoricalMaterialItemsAll"
Workbooks.OpenDatabase fl, strSQL, xlCmdTable
Set WB = Application.ActiveWorkbook
Set WS = Applicatio... Read more

Answer:Excel Macro - VBA code to import access data to excel

Hello, and welcome to the board!

When posting code, please use CODE tags, which extremely helps with readability.

Perhaps you could try using the code constant???...
Code:

Option Explicit

Sub ImportAccessData()
Dim dPath As String, sFile As String, strSrch As String
Dim TargetWB As Workbook, TargetWS As Worksheet
Dim sRow As Long, bFile As Boolean, strFlNm As String, strPath As String
dPath = "C:\Documents and Settings\Desktop\Auto Project\"
sFile = "*.MDB"
strSrch = dPath & sFile
sRow = 2
bFile = False
Set TargetWB = Application.ActiveWorkbook
Set TargetWS = TargetWB.ActiveSheet
If Dir(strSrch) <> "" Then
strFlNm = Dir(strSrch)
bFile = True
End If
Do Until bFile = False
strPath = dPath & strFlNm
Call GetData(strPath, TargetWB, TargetWS)
strFlNm = Dir()
If strFlNm = "" Then bFile = False
Loop
End Sub

Sub GetData(fl As String, WB As Workbook, ws As Worksheet)
Dim strSQL As String, iRow As Long, sRow As Long
strSQL = "SELECT HistoricalMaterialItemsAll.* FROM HistoricalMaterialItemsAll"
Workbooks.OpenDatabase fl, strSQL, 3 'xlCmdTable
iRow = 2
Do Until ws.Cells(iRow, 1) = ""
ws.Cells(sRow, 7) = ws.Cells(iRow, 5) 'Get the Description
iRow = iRow + 1
sRow = sRow + 1
Loop
Application.DisplayAlerts = False
WB.... Read more

1 more replies
Relevance 54.53%

Hi,
I want to merge two excel files into a 3rd one.

The data are in the files Book1, Book2 and the result in Book3.

I know that i can use VLookup to do the above. But what i have pasted is just an example. The real excel files contains about 9 columns with at many repeating row!!!

Any help will be much appreciated.
Thx
 

Answer:Excel - macro to merge 2 excel files using a common column

Hi welcome to the board. How are you going to treat the duble items, If you want to merge 2 files adn duplication occurs here must be one cell whihc is common to let's say Book1 and Book2.

Do you have a common value? Index? Whatever you call it?
 

3 more replies
Relevance 54.53%
Relevance 54.53%
Question: Excel and dates

Hi All,I am updating a old spreadsheet and wanted to add some work days down the side of it. The problem I have is that I only want Mon-Friday dates to be listed. For example....12/3/201212/4/201212/5/201212/6/201212/7/201212/10/2012etc.....For some reason I can't get it to work right. In this example you can see that the first work day for Dec is the 3rd and that the 8th and 9th are passed over because they are a weekend. I know I have done this before I just can't remember how I did it. I was able to click on a cell and drag down to highlite a months worth of cells and put in a formaula in the first cell and then all the dates would just show up below it. Anyone have any ideas?ThanksSpoiler

Answer:Excel and dates

Quote from: Spoiler on December 14, 2012, 10:10:41 AMI know I have done this before I just can't remember how I did it. I was able to click on a cell and drag down to highlite a months worth of cells and put in a formaula in the first cell and then all the dates would just show up below it. Are dates for all rows part of the existing data? If so, then one approach might be to add a column to show the day of the week and then use a Filter to show all days except Saturday and Sunday.

6 more replies
Relevance 54.53%

I have a column of calendar dates (A1:A100) that do not include Saturdays or Sundays. I have certain production times for various products that I must base off of these dates and am having trouble counting an amount of days that doesn't include any Saturdays or Sundays in my count. Example below.

10-02-2006 I need to add 18 production days to this date. Workdays only being Mondays-Fridays, I need the answer of the formula to equal 10-25-2006.

Can anyone assist?

Ron
 

Answer:Need Excel HELP with Dates

7 more replies
Relevance 54.53%
Question: excel dates tip

Can anyone let me know the formula for coverting a date held in excel as yyyymmdd into dd/mm/yyyy.Many thanks

Answer:excel dates tip

Assuming that is a text value try=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))where A1 holds the text date.

2 more replies
Relevance 54.53%

I'm trying to set up a spreadsheet so that each time I open it, the time between two dates is calculated. The two dates would be today (the day I open the spreadsheet) and a date about 10 years away. Any help would be appreciated.ThanxDD

Answer:Excel - between two dates

For examplein A1 30/03/2017in B1 =TODAY()in C1 =DATEDIF(B1,A1,"d")

10 more replies
Relevance 54.53%
Question: Excel dates

I have a friend from california he has got office over here in europ but because of work he needs too change his dates in excel too american format how can i do this?

Answer:Excel dates

Hopefully this guide will help.

2 more replies
Relevance 54.53%
Question: Excel Dates

I have been using this formula to let clients know when their bills are due:
=DATE(YEAR(A1),MONTH(A1),DAY(A1)+90)
this ends up looking something like this: 1/1/04
however, I have had a request from them to have it look more like this: 1-Jan-2005.
Any help would be greatly appreciated. Thank you.
 

Answer:Excel Dates

12 more replies
Relevance 54.53%
Question: Excel Dates

Eldest daughter driving me mad.At business she is working on a database in Excel.She tries to shorten the DOB entry as for example23/11/29 for someone born in 1929 but it comes out as 23/11/2029 yet when she enters 23/11/31 it comes out as she wants it as 23/11/1931. Is there something we need to format in Cells/Date?Brambles

Answer:Excel Dates

and have the same problem! Using excell 2000 and my cells are formatted correctly. Very strange, however there are lots of very helpful people on this site and here's hoping.Taffy

3 more replies
Relevance 54.53%
Question: Dates and Excel

I have a sheet in excel which I input my daily results. Some dates or blocks of dates are missing as I am unable to gather the data. (vacation, travel, etc)Column A is the date (mm/dd/yyyy)Column B is the result (0.00)I have another sheet with the following:Column A has a list of all dates (month and day only) (366 rows) Columns B through M have the year in row 1I need to lookup the month and the day and attach the year based on the column year (this is easy)=VLOOKUP(DATEVALUE(MONTH($A2)&"/"&DAY($A2)&"/"&YEAR(C$1)),Generation!$B:$C,2)BUT I need the exact date data returned. VLOOKUP returns the closest lower value.If no exact date then return 0 or "".Is there an easy way to do this in Excel without writing code?Thanks for helping in advance!

Answer:Dates and Excel

If you want an exact match, you need to use FALSE or 0 in your VLOOKUP as the optional range_lookup argument:=VLOOKUP(DATEVALUE(MONTH($A2)&"/"&DAY($A2)&"/"&YEAR(C$1)),Generation!$B:$C,2,0)Review the VLOOKUP Help file to see the options for this argument.If an exact match isn't found, this will return #N/A, so wrap the formula in an IF(ISNA()) function to get a "" of 0 if the VLOOKUP evaluates to #N/A. This will return "":=IF(ISNA(VLOOKUP(DATEVALUE(MONTH($A2)&"/"&DAY($A2)&"/"&YEAR(C$1)),Generation!$B:$C,2,0)),"",VLOOKUP(DATEVALUE(MONTH($A2)&"/"&DAY($A2)&"/"&YEAR(C$1)),Generation!$B:$C,2,0))

4 more replies
Relevance 54.53%
Question: excel dates

Filename: SysInfo.exe
Full Path: c:\Users\JIMBO\AppData\Local\Microsoft\Windows\INetCache\IE\RBAGCWGH\SysInfo.exe

____________________________

____________________________
Developers
Not Available

Version
1.0.0.2

Identified
6/27/2016 at 5:17:31 PM

Last Used
Not Available

Startup Item
No
____________________________
Many Users
Thousands of users in the Norton Community have used this file.

Mature
This file was released 4 years 8 months ago.

Good
Norton has given this file a good rating.
____________________________
Source File:
sysinfo.exe

____________________________
File Thumbprint - SHA:
8243b4ea661b060fe8cf4babc11ab5f51eadd28a0c9d66303183e8eceace8234
File Thumbprint - MD5:
Not available
 

Answer:excel dates

What is your problem and/or question?
 

2 more replies
Relevance 54.53%

I've got a strange one, I think !!!Current date is 16 Jan 2005, and in my module I say Dim CurrDate As LongCurrDate = Format(Now(), "00000")This sets CurrDate as 38369Later in the module I want to compare this date with one from a cell ...Dim MyNewDate As LongMyNewDate = Range("N" & MyStoreRow) ... value is 17 Jan 2005The value being returned in MyNewDate is 38369, not, as I would expect, 38370 ...In another worksheet, the new date in Nxx is 18 Jan 2005, and this returns a value of 38370, not 38371 ... Any ideas as to what's happening here ?!?! Is my Current Date wrong ? Is this a known 'problem' ?!?!?

Answer:Dates in Excel VBA (again !) ...

I'm not sure what is goung on but you would be better off working with Dates rather than LongsSub test()Dim mydate As Datemydate = Now()MsgBox mydateEnd Sub

6 more replies
Relevance 54.53%
Question: Excel and dates

I have two columns of dates, A and B. Column B is just As date plus 30 day. What i want it to do is, whenever i update column A's date, i dont want column B's date to change. how do i do that??

Answer:Excel and dates

i dont want column B's date to change.Remove the formula that updates B?MIKEhttp://www.skeptic.com/

24 more replies
Relevance 54.12%

I used a macro which worked totally fine under Excel 2003. Now, under Excel 2007 it does not run anymore but displays the following error message:"Run-time error '445':Object doesn't support this action.The function of the macro is to open up all other Excel spreadsheets located in the same folder and copy data out of them into the spreadsheet in which the macro is stored.When I click on "Debug" the Visual Basic Editor highlights the row which says: Set FilSrch = Application.FileSearchCan you help me with this? I've searched for a solution for a long time but could not find anything which worked.The following is the start of the macro code:Sub UpdateTable()Dim X As Range, I As Integer, J As Integer, RecNo As Integer, CopyVal As VariantDim SourceBk As Worksheet, DestBk As Worksheet, StartRow As Integer, IndRange As RangeDim ServRange As Range, SrcOpen As Boolean, SourceName As StringDim FilSrch As Object, MyFilArray() As String'MsgBox "This function not available in this version.", vbInformation, "Function Not Available"'Exit Sub'Sheets("LookUp").[a12] = NowWith Application .ScreenUpdating = False .StatusBar = "Counting source files ..."End WithSet FilSrch = Application.FileSearchWith FilSrch .NewSearch .LookIn = ActiveWorkbook.Path .FileType = msoFileTypeExcelWorkbooks If .Execute > 0 Then ReDim MyFilArray(.FoundFiles.Count) For I = 1 To .FoundFiles.Count If .FoundFiles(I) <> ActiveWorkbook.Path & "\" & ActiveWorkbook.Name Then ... Read more

Answer:Excel 2003 Macro doesn't run under Excel 2007

As far as I know, Application.FileSearch is not available in 2007. Searching for filesearch replacement turns up lots of hits with various workarounds, such as this:http://groups.google.com/group/micr...Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 54.12%

Hi guys,

I have been on the lookout for a macro that will compare 2 worksheets within MS Excel and output the differences on a third sheet.

I am being directed to the URL below but cannot for the life of me work out how to implement this:

http://exceltip.com/st/Compare_two_worksheets_using_VBA_in_Microsoft_Excel/477.html

There are two snippets of code which I will include here for convenience but not sure whether these should be pasted into Workbook Code (i.e. right-clicking Excel icon left of "File" - top-left), Worksheet Code (i.e. right-clicking worksheet tab) or inserting a new Module - or some other method. Is there anyone here that could guide me to how to implement this code?

Quote from webpage - "With the macro below it is possible to compare the content of two worksheets.
The result is displayed in a new workbook listing all cell differences. "

Code:
Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
Dim r As Long, c As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long
Application.ScreenUpdating = False
Application.StatusBar = "Creating the report..."
Set rptWB = Workbooks.Add
Application.DisplayAlerts = False
While Worksheets.Count > 1
Worksheets(2).Delete
Wend
Application.DisplayAlerts = True
With ws1.UsedRange
lr1 = .Rows.Count
lc1 = .Column... Read more

Answer:Solved: How to use this Macro in MS Excel (compare excel worksheets)

Apologies... I have sorted this... For those that might be looking at this for help here's what I missed:

These are both Sub's that should be created as a Module - both in the same module. I am just comparing worksheets within the Active Worksheet so just removed:

' compare two different worksheets in two different workbooks
CompareWorksheets ActiveWorkbook.Worksheets("Sheet1"), _
Workbooks("WorkBookName.xls").Worksheets("Sheet2")
Click to expand...

I was then able to run this from Macro's. All sorted.

Apologies again
 

1 more replies
Relevance 54.12%

With due respect and credit to DerbyDad03 whose earlier post gave me the code below; is there any way to have the new worksheets created from a variable length field e.g. a persons name in a list or a field of fixed length other than a date as the script below uses?Sub CreateMonthlySheets()Dim lastRow, mMonth, tstDate1, tstDate2, shtName, nxtRowOn Error Resume Next'Turn off ScreenUpdatingApplication.ScreenUpdating = False'Make a copy of the data sheet and sort by date Sheets("Sheet1").Copy After:=Sheets(1) Sheets(2).Name = "SortTemp" With Sheets("SortTemp") lastRow = .Cells(Rows.Count, 1).End(xlUp).Row Rows("2:" & lastRow).Sort Key1:=Range("A2"), Order1:=xlAscending 'Using SortTemp Sheet, create monthly sheets by'testing Month and Year values in Column A'Loop through dates For Each mMonth In .Range("A2:A" & lastRow) tstDate1 = Month(mMonth) & Year(mMonth) tstDate2 = Month(mMonth.Offset(-1, 0)) & Year(mMonth.Offset(-1, 0)) 'If Month and Year are different than cell above, create new sheet If tstDate1 <> tstDate2 Then ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count) 'Name the sheet based on the Month and Year ActiveSheet.Name = MonthName(Month(mMonth)) & " " & Year(mMonth)'Copy Column Widths and Header Row .Rows(1).Copy ActiveSheet.Rows(1).PasteSpecial Paste:=8 'ColumnWidth ActiveSheet.Rows(1).PasteSpecial 'Data... Read more

Answer:Excel Macro to create new worksheet in Excel 2010

I created a quick macro to help you out. This macro creates a new sheet and names it using the contents of cell "A1".Sub CreateNewSheet()
Dim NewSheet
Dim SheetName As String

SheetName = Worksheets("Sheet1").Cells(1, "A").Value
Set NewSheet = Sheets.Add(After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet)
NewSheet.Name = SheetName

End Sub
Law if Logical Argument: Anything is possible if you don't know what you're talking about.

4 more replies
Relevance 54.12%

I run a weekly excel 2007 macro that converts a spreadsheet to a Word document, but it stops after row 29 of data. Any spreadsheet with 29 rows or less works fine. If I have 34 rows of data, the macro hangs. See below for script. Any help would be appreciated.

Public Sub AddControls(WrdApp As Word.Application, CurRow As Integer)
Dim OptChecked As Boolean
Dim GrpName As String
GrpName = "Grp" & CurRow
'Calculate the colors based on the cell information.
CurRange = "F" & CurRow & ".." & "F" & CurRow
Range(CurRange).Select

If InStr(1, UCase(ActiveCell.Text), "X SRM") > 0 Then 'SRM
OptChecked = True
Else
OptChecked = False
End If

SetCellBG WrdApp, True
WrdApp.Selection.TypeText Text:="Governance:" & vbCrLf

Set RadioObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.OptionButton.1")
With RadioObj.OLEFormat.Object
.GroupName = GrpName
.Caption = "SRM"
.Font.Name = "Arial"
.Font.Size = 8
.Value = OptChecked
End With

If InStr(1, UCase(ActiveCell.Text), "X PPO") > 0 Then ' PPO
OptChecked = True
Else
OptChecked = False
End If

WrdApp.Selection.Collapse Direction:=wdCollapseEnd
WrdApp.Selection.TypeParagraph

Set RadioObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.OptionButton.1")
With RadioObj.OLEFormat.Object
.GroupName = GrpName
.Caption = "PPO"
.Font.Name = "Arial"
.Font.Size = 8
.Value = O... Read more

Answer:Excel Macro - converting excel to word doc - stops after row 29

13 more replies
Relevance 54.12%

Does anyone know how to run Excel 2003 print macro on Excel 2007 or higher? When I tried running my 2003 macro, the VBA editor pops up w dialogue box, " Run-time error '1004': Method 'ActivePrinter' of object'_Application' failed ".

This is my code: Application.ActivePrinter = "Epson LQ-300 ESC/P 2 on USB003"

Can anyone help?

Thanks!
 

Answer:How to run Excel 2003 print macro on Excel 2007?

Welcome to TSG hope we can help you and you can help others.

I'm not an Excel macro guy but you code looks reasonable. What happens if you run this. I suspect printer name is a little off.

MsgBox "The name of the active printer is " & Application.ActivePrinter
 

1 more replies
Relevance 54.12%

Hi, I have created an excel macro that runs sensitivities in my model. The excel button runs 5 different cases.

The macro runs fine and does as I want it to.

Once the macro has run, and I go to edit a cell and hit "F2" excel crashes. This only seems to happen in certain cells and not all of the time.

Does anyone have experience with similar questions?

Thanks
 

Answer:Excel Macro runs fine..then excel crashes

11 more replies
Relevance 54.12%
Relevance 54.12%

I want to have an IF true/false statement stating that for a column of DATES, if the date is before "5/1/2013" TRUE and everything else false within the if statement. thank you in advance!

Answer:How do a lay out an EXCEL IF statement using DATES?

Try this:With your column of DATES in column A,put this formula in cell B1 and drag down.=IF(A1<DATE(2013,5,1),"True","False")What happens if the date is exactly 5/1/2013?See how that works for you.MIKEhttp://www.skeptic.com/message edited by mmcconaghy

2 more replies
Relevance 54.12%

i want to sum numbers between date ranges how do i do this?

Answer:excel formula for dates

Try using =DATEDIF()=DATEDIF(1stDate,2ndDate,"Interval")1st Date : Earliest date. 2nd Date : Most recent date. "Interval" : What you want calculated. Must be surrounded with quote marks. Intervals can be: "d" Days between dates."m" Months between dates."y" Years between dates."yd" Days between the dates, as if the dates were in the same year."ym" Months between the dates, as if the dates were in the same year."md" Days between the two dates, as if the dates were in the same month and year.MIKEhttp://www.skeptic.com/

6 more replies
Relevance 54.12%

When cells are formatted as date in Excel 2000, they change to a 5-digit number (starting with 3). Has anybody come accross this, and does anybody know a way around?
 

Answer:Dates in Excel 2000

9 more replies
Relevance 54.12%

I have crated a large excel spreadsheet where dates are in 3 columns ie day/month/yearIs there a way to get the dates in a single columnthanks in advancebbmf

Answer:dates in columns on excel

Assuming that day, month and year are numbers in columns A, B and C=DATE(C1,B1,A1)

2 more replies
Relevance 54.12%

Hi,

Need Urgent help. Need a formula, explained in detail in attachment and trying to explain here

Column A (Inv Value) Column B (Col.Date) week1 (14/4/2012 - 18/4/2012)
4500 1/5/2012 0 (formula)
225000 23/4/2012 0
55393 17/4/2012 55393 (formula)

* Column A and B are Data field from where we will take data.
* Column C and D are the fields where i need to put formula

**** If column C date range is in Column B date, then put the value of Column A
I can split Column c in two cells with start date and end date if needed (then merge remaining cells)

Appreciate your help and support on urgent bases to solve my issue. < removed email address - etaf moderator >
 

Answer:Excel Formula - Dates

11 more replies
Relevance 54.12%

This is multi-part question on how to create different formulas on the attached worksheet.

#1 - I would like to count the number of Start Dates IF the Start Date is blank or greater than today, but only IF there is a date in the Requisition Date column. This would be to indicate the total number of open positions as of today.

#2 - I would like to count the number of cells in column A that are greater than or equal to 17 IF the Start Date is blank or greater than today, but only IF there is a date in the Requisition Date column. This would indicate total number of open positions that have a grade lever of 17 or above.
 

Answer:Excel-Counting with dates

7 more replies
Relevance 54.12%

How do I write an If/Then statement in excel to change data to red if it's 6 months old? I have one field for the current date, B2, and a column of dates, B6-B40, that I want to know if the dates are older than 6 mo from today's date. If they are older then 6 months from today's date, I want the dates in column B and their corresponding data in column A to turn red. Any ideas? Thanks in advance.

Answer:if then statements with dates in excel

See if this works for you:This is for Excel 20071) Select your cell or Range of Cells: B6-B402) On the ribbon click Conditional Formatting3) Click on New Rules, it?s near the bottom of the dialog box.4) Click Use Formula to determine which cells to format.5) Enter the formula: =DATEDIF(G3,TODAY(),"M")>6=DATEDIF(B6,TODAY(),"M")>66) Click on the Format button7) Select the Fill Tab8) Select a pretty color9) Click OK10) Click OKThis will highlight the cells that are GREATER THAN Six months.For Six Month OR GREATER use:=DATEDIF(G3,TODAY(),"M")>=6=DATEDIF(B6,TODAY(),"M")>=6EDITED, did change the cells from my test sheet.MIKEhttp://www.skeptic.com/

3 more replies
Relevance 54.12%

excel insists on changing the year I enter (such as 2005) to the current year (2011) after I have specifically formatted the cells in the date format I selected. For example, I enter 5/2005 and end up with 5/2011. No matter what date format I select from the drop-down, it keeps doing this.

Answer:why does excel change my dates

I formatted some cells as Custom m/yyyyWhen I enter 5/2005 it displays 5/2005 and shows 05/01/2005 in the fomula bar.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 54.12%

I want to put hourly rates of $6 alongside weekdays and $7 for weekends.I did this it doesn't work:=IF(A2=weekday, 6, 7)Help?

Answer:EXCEL If else statement with dates

The WEEKDAY function must refer to a cell that contains a date.Assuming your system is set up such that Sunday is weekday 1 and Saturday is weekday 7, this should work:=IF(AND(WEEKDAY(A2)<>1,WEEKDAY(A2)<>7),6,7)For any WEEKDAY not equal to 1 or 7, the function will return 6.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 54.12%

Hey
I have imported data from an outside database and it brought in the dates seperated. The information is in columns and the day, month (which is in text), and year each have their own cell. There are hundreds of dates. Due to space considerations I need each date to only take one cell. Is there any way to do this without having to do it by hand?
 

Answer:Solved: Dates in Excel

Not sure which version of Excel you are using, but am assuming 2007. I will also mention what to do if you are using 2003 or earlier

Create a blank worksheet and do the following so that you can see how the following formula works and then how to convert it as a value rather than a formula

In A1 type 24
In B1 type June
In C1 type 1990
In D1 type =DATEVALUE(CONCATENATE(A1,B1,C1))

Assuming you have similar data in the Columns A, B and C, copy the formula in D1 down column D until you have a formula for each row of data in A, B, C

Select all the formulas in column D and click on Home Ribbon tab ans click on the Copy Icon to place it in the clipboard. 2003 and earlier Edit, Copy

Whilst those cells are still selected click on Home ribbon tab and click on the little down arrow just under the Paste button and select paste values. 2003 and earlier Edit, paste Special, Values OK

Your data in column D will now be as a date (not a formula) which in the background is treated as a number, which then allows you to do calculations on the dates.

Now select columns A, B and C and delete those columns

You are now left with column A and the dates in single cells.

Hope that helps
 

3 more replies
Relevance 54.12%

Hey there,I would appreciate if i get help on Excel Date formatting. Problem Statement: when I download the report from Business Intelligence, I get certain columns with Dates. The cell values are so rigid that, I can't format the dates which are in System driven format, and few are easy to convert to MDY, or DMY or whatever. But few, I cannot change. Second Problem: When all the data is related to Quarter 1, when I try to format the date, I end-up getting dates as 02-Sep-2011, but the actual date is 02/09/2011 when I pull it from reporting system.The date should be 09-Feb-2011. I have some 3000 lines of such adamant dates, help me to get out of this issue.Positively seeking anybody's help.Gracias/Thanks/Saludos/Dhanyawad/Shukriya

Answer:Adamant Excel Dates...

re: "I can't format the dates which are in System driven format,"I don't know what you mean by "System driven format" but I can tell you that very often data that gets downloaded from websites and/or other applications ends up in Excel as Text instead of numbers or dates.Since I can't see your spreadsheet from where I'm sitting, I'm going to take a guess and suggest that both of your problems are related to each other.Let's start with Problem 2:re: I end-up getting dates as 02-Sep-2011, but the actual date is 02/09/2011 This statement does not really make sense. 02/09/2011 could actually be 02-Sep-2011 or it could actually be 09-Feb-2011, depending on how Excel (actually Windows) is set up. Let me explain.The format in which Excel recognizes dates is based on the settings in the Regional and Language Control Panel, Customize button, Date tab. This is what determines whether the first 2 digits represent the day or the month.My guess is that the values that you cannot format as dates are those where Excel can not recognize the month.For example, since your system is recognizing 02/09/2011 as 02-Sep-2011 I'll assume that your Windows setting is something like dd-mmm-yyyy.Excel has no problem changing 02/09/2011 to 02-Sep-2011 since 09 is a valid month, but it wouldn't know what to do with 02/15/2011 since there is no 15th month. My guess is that Excel decides that values like 02/15/2011 must be text and therefore cannot be formatted as a date.Try changing your Control panel s... Read more

3 more replies
Relevance 54.12%

I am trying to subtract two dates in excel but I keep getting weird results...

My dates are formatted like this: 8/11/2010

I am using TODAY() to return the current date. Then I want to compare that to see how many days have passed since a given date. So my formula is this: =TODAY()-D2

Now, it seems to kind of work because the result I get is 1/30/1911. 30 days is the correct answer! But why am I getting the full date?? How can I fix this? And why the heck is it saying 1911 - on another computer I tried it says 1/30/1900...

??
 

Answer:subtracting dates in excel... help please!

Look into the Datediff function. I think for what you want (doing this from the top of my head) is Datediff("d", D2, TODAY()) or you can sub TODAY() for NOW(). Like I said it is from the top of my head but should be something like that.
 

3 more replies
Relevance 54.12%

how do i write a formula for this. if priority high add 2 days to date raised, if priority low add 14 days to date raised?

Answer:Excel formula to add dates

What do you mean by "date raised"?How will Excel know what is a High priority and what is Low?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

4 more replies
Relevance 54.12%

Hi there.

I am trying in Excel 2007, to do the following:

A guy phones in a fault. The date and time are recorded. When the fault is fixed, the date and time are recorded again. I need to calculate the response time, but I need it to only look at business hours, which are 8 to 5, Monday through Friday, when calculating this. Some faults may stretch over more than one working day, or over a weekend.

Is there a simple way to do this?

Thanks in advance for any help.
 

Answer:Excel, dates and times

I think you'll have to Google for this

I can suggest the following link for starters:
http://www.cpearson.com/Excel/datetime.htm#WorkHours
 

2 more replies
Relevance 54.12%

Working with Excel 2000 is there a way to filter dates?

Thanks for your help.
 

Answer:EXCEL - Filter Dates

8 more replies
Relevance 54.12%

I have a strange problem I hope someone can shed some light on

I want to make a new column of months from a date
I have a detached with column A as a normal date format 01/12/2006 DD/MMM/YYYY
I then in column B use the month function - so = month(A1)
it displays 12 - and if I go onto the Fx to see the expression helper / wizard
I get told that i get a number returned 1-12 here 1 = Jan and 12 =dec

so that i format the =month(a1) into a MMM-YY format and
get Jan-1900 - I'm cool with the 1900 as i have not specified YYYY but why do i get Jan returned instead of Dec

But what I'm after and maybe a simpler way is to have a dropdown on my pivot table of MM-YYYY from my date field rather than DD-MMM-YYYY

so I can choose and show month data

I'm sure I did this in the past just by using format - MMM-YYYY on a date field and it worked in the picot table OK

any help appreciated
 

Answer:Excel: Dates: Month

7 more replies
Relevance 54.12%

I would like to use the countif funcion to count how many times a product appears in a column by month. For example, I have two columns - Date & Product. The dates span over one year and I would like to know how many times a product appears each month.Hope you can help.Thanks, Mary

Answer:Excel counting dates

Assuming dates in column A and product names in column B:To count the number of product X sold in September (Month 9)=SUMPRODUCT((MONTH(A1:A10)=9)*(B1:B10="X"))

2 more replies
Relevance 54.12%

Does Excel (version 9.0) have a feature which will allow me to compute various day/month/year time lapses? For instance can I compute the number of months between Oct. 1, 2003 and September 31, 2013? Or if I want to determine what day it is 25 years from today? I can do this on my handheld, but would like to design an Excel formula for it. Any help would be appreciated.
 

Answer:Computing Dates in Excel

Use the datedif function

i.e. =DATEDIF(Date1,Date2,"Interval")

Where
Date1 is Start Date
Date2 is the end date
Interval is one of the following: You must use the inverted commas

"m" Months
The number of complete months between Date1 and Date2.

"d" Days
The number of days between Date1 and Date2.

"y" Years The number of complete years between Date1 and Date2.

"ym" Months Excluding Year
The number of months between Date1 and Date2, as if Date1 and Date2 were in the same year.

"yd" Days Excluding Years
The number of days between Date1 and Date2, as if Date1 and Date2 were in the same year.

"md" Days Excluding Months And Years
The number of days between Date1 and Date2, as if Date1 and Date2 were in the same month and the same year.
 

1 more replies
Relevance 54.12%

Number of working hours between two dates. Working hrs 6:00 AM to 8:00 PM. Including Saturday, excluding Sunday and Holidays

More replies
Relevance 54.12%

Probably a simple solution to a fairly simple question but it's had me stumped for ages.In the first column i typed in a date (26-03-08)what i then want to do is drag this date down and for it to increase in value by weeks ie 02-04-08, 09-04-08 etc. Can it be done, or have i got to go through and type it all out (which would have been quicker now with the time i've spent mucking around).Many thanksNos

Answer:creating dates in excel

Providing the column is formatted as Date then enter the first two dates, select both cells and drag down.

2 more replies
Relevance 54.12%

Number of working hours between two dates. Working hrs 6:00 AM to 8:00 PM. Including Saturday, excluding Sunday and Holidays

More replies
Relevance 54.12%

I am trying to do the following:If the date is before 1/1/2014 in cell I15 I want cell J15 to say "yes". If the date is after 1/1/2014 in cell I15 I want cell J15 to say "no".Then, in another cell, I want if cell I15 is between 1/1/2014 and 1/1/2015 I want the value in G15 to be placed in cell K15.

Answer:If Statement in Excel with Dates

For the J15 formula, what do you want to return if the date in I15 is equal to 1/1/2014?The same question holds for the formula in K15: What if the date equals one of the 2 dates that you are checking for?BTW, the solution will be based on the IF function. Have you read anything on the IF function in the Excel Help files or via a Goolge search? Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 54.12%

Some times I have only the year (eg 1914). Other times I have the full date (eg 12/25/2011). If I format the Excel for as a date and enter merely 1914, it shows as 3/28/1905 (its reading only the "1914"). I do NOT want to enter 1/1/1914 - because that is an incorrect date (all I have is the year 1914). What I want: If I have only the year, I want only the year (eg 1914) to show. If I have the full date, I want the full date (eg 12/25/2011) to show. How do I do it? Does it require an IF statement?

Answer:Formatting Dates in Excel

Not sure what it is your after, but Excel stores dates and times as a number, representing the number of days since January 1, 1900So when you enter 1914, Excel reads it as 1,914 days since 01/01/1900or 03/28/1905.See this page for a complete explanation of how Excel works with Dates/Times:http://www.cpearson.com/excel/datet...MIKEhttp://www.skeptic.com/

5 more replies
Relevance 54.12%

Hi All,

I have spent all day trying to figure out how to do this and when i think i have done it.. something is missing!!

So, when the date in column A = Today, i need an email to be sent to the email address in column F stating the below:

"Hi All,

Engineer (Column B), is visiting Customer (Column C) today at (Column D) to complete (Column E)

Many Thanks"

I just cant get it to work!!

Please can someone advise what i'm doing wrong?

Sub eMail()
Dim lRow As Integer
Dim i As Integer
Dim toDate As Date
Dim toList As String
Dim eSubject As String
Dim eBody As String
With OutLook.Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
If (Cells(i, 1) = TODAY < "") Then
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
toList = Cells(i, 7)
eSubject = "Engineer " & Cells(i, 2) & "job list " & Cells(i, 1)
eBody = "Hello All, " & vbCrLf & vbCrLf & "Engineer" & Cells(i, 2) & "Is at customer" & Cells(i, 3) & "in" & Cells(i, 4) & "and is" & Cells(i, 5) & vbCrLf & vbCrLf & "Many Thanks"

On Error Resume Next
With OutMail
.To = toList
.CC = ""
.BCC = ""
.Subject = eSubject
.Body = eBody
.bodyformat = 1
.Send
End With

On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
Cells(i, 5) = "Mail Sent " & Date + ... Read more

Answer:Emailing from Excel on certain dates

16 more replies
Relevance 54.12%

I am trying to change some dates in a column in excel 2007. I want the date to read the month and year but the year that I need is 2007 and the cell wants to automatically change to 2008. How can I prevent this from happening?

Thanks for the help
puterputter
 

Answer:Dates in Excel 2007

6 more replies
Relevance 54.12%

I want to have two dates within the if statement. This is what I made, but i need it corrected. =IF(J2>DATEVALUE("5/31/2014"),"GOOD","EXPIRED" J2>DATEVALUE("6/31/2014"),"GOOD","EXPIRING SOON")

Answer:EXCEL If Statement Using Dates

Fieldman,Sorry it's taken me a day to get back to you, I've been pretty busy...I don't know if I understand what you're wanting to do or not, but the only way that I'm aware of to put multiple "IF" statements into one field is to do it the way I demonstrated above. You can put as many as you'd like in there to achieve what you're trying to do.This is just a shot in the dark, but I've re-read your posts several times now and am wondering if it would work the way you want if you change the formula in A2 to something like this:=IF(A1>DATEVALUE("5/31/2014"),"GOOD",IF(A1>DATEVALUE("6/31/2014"),IF(A1>DATEVALUE("5/31/2014"),"EXPIRED","EXPIRING SOON"),"EXPIRING SOON"))I really think you will need to play with the formula above to make it work, but I think something like this is going to be your best bet.Law of Logical Argument: Anything is possible if you don't know what you're talking about.

10 more replies
Relevance 54.12%

I am working with an Excel file and I have today's date autopopulating in cell C1 using the NOW function. In cell b12 I enter the date of an instance, for example 4/15/2013, then in C12 I am using the function of IF(B12="","",SUM(B12+30)) to give me 30 days from the date. In cell H12 I am trying to have that cell autopopulate if the date in C12 is older than today's date in C1. I have written the formula the way I feel it should work, but it is not working yet. Here is what I have =IF(C12="","",IF(C12<C2,"","X")). Once I get that working there is another formula for me to add where it turns the line red if there is an X in H12. It seems like a lot of stipulations and connections and I know if one thing is off, then more than likely even more will not work.Thank you

Answer:Excel if then statements using dates

I don't see where you are using C2 for anything, so I can't answer any question related to IF(C12<C2,"","X").BTW, if you are only dealing with Dates, why not use =TODAY() instead of = NOW()?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

4 more replies
Relevance 54.12%

Hello to all,

recently my excel started to change the dates lets say from 02/01/08 to 06/08/02 etc. But it doesnt change all dates just some. Please, help me with this.

My operating system is Windows XP.

Thanks,
 

More replies
Relevance 54.12%

Hello,
What a great help this forum is.
I know nothing at all about VBA, yet managed to get a macro in excel that allows me to send the "due date" emails that works almost perfectly for my needs. I based my macro on the code found here https://forums.techguy.org/threads/...ue-date-reminder-based-on-excel-file.1129238/
But i'm still missing one function in this macro for it to be perfect for my needs. I want it to send only one email to the "area manager" specifying all the items (serial number and location) that follow my due-date rule (instead of one email per item). I want it to send a list of items that are due this month for example.
Is it possible?
 

More replies
Relevance 54.12%

Hi,I am looking to write If statement that if I write a date into cell A2, then A1 will display "Good" if its within 4 years since the date in A2, or A1 will display "Expiring Soon" if its between 4-5 years after A2, or A1 will display "Expired" if its 5 years after the date in A2. I have never used If statements with dates before so I don't know where to start and any help will be greatly appreciated.Thanks,

Answer:How to If Statements Using Dates in Excel?

re: "if its within 4 years since the date in A2"By its I assume you mean today's date. If so...Take a look at the EDATE function. EDATE returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date).e.g. For your 5 year (60 months) expiration criteria, this should work.=IF(EDATE(A2,60)<TODAY(),"Expired","")Basically what this says is "If I add 60 months to the date in A2 and the result is less than today's date, then the date in A2 must be more than 5 years ago."The "inverse" of that is to subtract 60 months from today's date to get the same result:=IF(EDATE(TODAY(),-60)>A2,"Expired","")Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.message edited by DerbyDad03

3 more replies
Relevance 54.12%

I am having trouble figuring out how to write the correct function using countif. I have two columns. One column has the start date and the other column has the end date. I want a separate cell to countif those two dates are between the specified dates.

start date end date
jane doe 1/22/2008 4/3/2008

February ?

So for February I need to countif those two dates fall between 2/1/2008 and 2/28/2008.
Please help.
 

Answer:countif excel between two dates

16 more replies
Relevance 54.12%

Dates input to Excel are being treated simply as a text string and not recognised as dates. Apparently, all was OK until recently (this is a friend's problem). Old spreadsheets suffer the same problem, so it seems to be a global problem.Any ideas please?

Answer:Excel not recognising dates

The cells have to be formatted to accept dates rather than text or numbers. Goto Format/cells after selecting those that need to be dates.

6 more replies
Relevance 54.12%

Quick question.

Lets say I have a date.

11/01

Is there a formula that would return and IF statement?

For example

I am looking to all the dates between 12/15 and 01/15

I have tried to create a formula using the IF statement, but cant only get the first half.

=IF(A1<=12/15,"TRUE","FALSE")

But how do I get the second half for it to check if if the date is less then 01/15? every time I try I get an error.
 

Answer:Solved: Excel Dates

Howdy. If I understand correctly. Be sure to check year, since the year will be in the cell whether it displays or not.

=IF(AND(A1<=12/15,A1>01/15),"TRUE","FALSE")
 

3 more replies
Relevance 54.12%

Good day i would like to ask how to make an if statement with datesit would appear like thisa1=any date depending on expected deliveryb1=date receivedc1=either "Delayed" or "Ontime"IF A1<=b1 then c1="Ontime" else c1="Delayed" Endsomething like this for excel..

Answer:Excel coding using if for dates

Here is what you asked for:IF A1<=b1 then c1="Ontime" else c1="Delayed" EndThe syntax of an IF function is =IF(logical-test, value-if-true, value-if-false)=IF(A1<=B1,"Ontime","Delayed")However, I have a question.If A1 is the expected delivery date, and it is less than the date received, isn't the package Delayed?What am I missing?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

7 more replies
Relevance 54.12%

Hello,I am trying to do the following in Excel:If the date in column Q1, is 30 days over the date in column P1, outline or fill Q1 in a color. Is this an IF statement or conditional formatting?Thank you!

Answer:Excel if statements with dates

Conditional formatting is what your looking for:1) Select your cell, Q12) On the ribbon click Conditional Formatting3) Click on New Rules, it?s near the bottom of the dialog box.4) Click Use Formula to determine which cells to format.5) Enter the formula: =IF(P1+30<=Q1,TRUE,FALSE)6) Click on the Format button7) Select the Fill Tab8) Select a pretty color9) Click OK10) Click OKMIKEhttp://www.skeptic.com/

2 more replies
Relevance 54.12%

I would like to build a spread sheet so when I enter a date the adjacent cell will have a date according to some rules. For example

Dates entered between Jan-01-2013 and Mar-31-2013 the adjacent cell will have a date of July-01-xxxx (where xxxx is the same year as the entered date. Can someone help me out with this.

thanx
 

Answer:Solved: excel dates

9 more replies
Relevance 54.12%

This should be simple and I thought I had it nailed, but the formula is not working, so I am obviously doing something wrong.I have two adjacent columns. The first column is always populated with a date and the second column may be populated with a date or may be blank. I am trying to determine if one or both dates are greater than 3/1/2008, then the row is "OK"; if neither date (or if the second column is blank) is before 3/1/2008, then that row is out. Here's what I tried to use: =IF(OR(H2>DATEVALUE("03/01/2008"),I2>DATEVALUE("03/01/2008")),"OK""OUT").The formula just does not return the correct result. Thanks for the help.

Answer:nested if excel with or and dates

You are missing a comma between "OK" and "OUT"Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

17 more replies
Relevance 54.12%

I have an excel document. How can I get excel to automatically populate several fields based on a date in another field. For example: If field A5 has a date of 02/01/2011..how do I have Excel automatically generate fields C6 thru C14 with dates of 2/1 thru 2/8? The fields in C6 thru C14 would change based on the date entered in C5. Any help is greatly appreciated

Answer:If and Then Statements with Dates in Excel

In C6 enter this:=A5In C7, enter this and drag it down as far as you need:=C6+1Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

9 more replies
Relevance 54.12%

Is there any way I can get a value produced that is the previous week day, ie not a weekend, that I can then export into notepad? To be run automatically everyday?
 

Answer:Solved: Excel dates

10 more replies
Relevance 54.12%

In an excel spreadsheet, I have two colums, each with a date in it. I want the third column to yield the greater of these two dates. For example, column one has 11/10/09, column two has 12/15/10, I want column three to show 12/15/10 as the greater of these two dates. Thanks.

Answer:excel if then statements with dates

Try this:If 11/10/09 is in cell A1andIf 12/15/10 is in cell B1in cell C1 enter the formula: =IF(A1>B1,A1,B1)MIKEhttp://www.skeptic.com/

4 more replies
Relevance 54.12%

HiCould you please let me know how can I add up the totals if when one the column contains dates? SUM( ( Sheet1!A2:A7 = "in" ) * ( Sheet1!B2:B7 ="Jan-10") * Sheet1!C2:C7 ) it return 0... ideally would be to get the total from c column if A column = in, and B column = Jan feb and march. is it possible?

Answer:Excel sum formula with dates

B2:B7 ="Jan-10"Is it January 10th 2010 orJanuary 2010?MIKEhttp://www.skeptic.com/

9 more replies