I have a for/next loop working through a date range. I want to skip a date at the 'next' code line.
1st loop Date = 1/1/2008
miss 2/1/2008
2nd loop Date = 3/1/2008
For Each Date In Rng
Sheet1.copy
Sheet2.copy
Next Date+2
column 1 is vouchers
column 2 is date
vouchers column is populated with a voucher code for every date date column is populated with dates beginning with today, ending with the last day of the year.
I would like to create a function that evaluates the date file, If date = today's date, return value in the adjacent vouchers column.
I've been using the following code and would like to add another IF that checks the value of a cell to see if it holds today's date.
Code:
If Range("E" & CStr(LSearchRow)).Value "Closed" Then
If Range("E" & CStr(LSearchRow)).Value "Closed w/o Customer Confirm" Then
'Select SR in Sheet to update
Range("B" & CStr(LSearchRow)).Select
[Code] ......
I tried to modify it to...
Code:
If Range("E" & CStr(LSearchRow)).Value "Closed" Then
If Range("E" & CStr(LSearchRow)).Value "Closed w/o Customer Confirm" Then
If Range("AK" & CStr(LSearchRow)).Value "=today()" Then
'Select SR in Sheet to update
[Code] ......
I've tried a couple of other things but no success
I have a pivot table for which I load data from a .csv file. Is it possible to include a timestamp on when the .csv file was last loaded. If I do it above the actual pivot table, it just shows when the pivot table was refreshed, but not when the .csv file was last loaded.
View 4 Replies View Relatedhow to put this in
If the date in C3 = January 31 then do something.
I don't know how to put the date part in code.
The code is ok until I hit a month with 4weeks in it and days left over.
The code puts a week total in place of a Sunday, but as some months end before a Sunday appears the code just builds a Month end total sheet.
What I need in this case is a Week total even if there is no Sunday before it builds the Month end Total.
I hope i'm being clear
Sub NewSheets()
Dim Dte As Date, Dy As Date
Dim i As Long, j As Long, Dys As Long
Dim CountWeek As Boolean
Dim Shts As Long...
I have got a spreadsheet that needs filling in by 3pm everyday. The workbook has a worksheet for each day e.g. Mon to Fri. What I am looking to do is fill the worksheets in for whatever day it is e.g. if it is monday then select monday data from required file and paste into monday worksheet. I was going to create five macros for each day and then select the required one on the day, but rather than doing this is their a way that i could write one macro that runs by checking what day it is? e.g. if monday run monday code...
View 5 Replies View RelatedI have a formula that tries to determine if a date is between two dates. The formula I have is =IF(AND(A1>=A2,A1<=A3),"yes","no")
a1 = 7/31/2009
a2 = 8/1/2008
a3 = 3/31/2010
If the date is between those two dates then "yes", if not then "no". I tried this formula a couple times and it works when a3 is 3/31/2010 but when I try 3/31/2009 for example it does not work. So it seems like there is something wrong with the second date I am referencing.
I'm trying to use a list of date of births to determine an age output.
However the date of births are all in a simple reverse formate, eg
19790822 would be 22nd August 1979
Ideally, i would take this number and the output would just be the persons age in whole years.
I am trying to determine if a specific date is what week in a 4-4-5 accounting month. See attached file. Is there a formula that can be used to do this???
View 4 Replies View RelatedI'm trying to write a batch file that connects to a database and passes in an SQL query. One of the conditions of this query is the date. I need the batch file to figure out what todays date is and pass that in.
This is what I currently have.
ws_tran_date between to_date( '&datefrom 08:30:00AM', 'DD-MON-YYYY HH:MI:SS AM') and TO_DATE('&dateto 08:30:00am', 'DD-MON-YYYY HH:MI:SS AM')
When I run the batch file, it asks me to input the dates manaully in the format DD-MON-YYYY (eg 7-NOV-2006). I need the batch file to figure this out on it's own.
Is there some function I can call to do this?
I am attempting to create a template to input specific dates. If a student submits an assignment on the due date, I have set up the spreadsheet to conditionally format whether the assignment was late or not. In addition, I have a column for when feedback is to be submitted and whether it was on time or not. My problem is this...
I cannot figure out a formula or a way to determine:
If the student submits the assignment late, when will the feedback be due? For example, if submitted one day late, the instructor has an extra day to submit feedback.Two days late is an extra two days to work on it etc..
I am looking for a macro that will run on worksheet activation. it needs to run only if the file had been saved from the previous week. would something like this work?
Private Sub Worksheet_Activate()
If application.worksheet.lastsaved = msolastweek Then
range("RANGE").clearcontents
End If
End Sub
See attached spreadsheet. I have a set of Jobs with a set of end dates. A meeting date should be held after the end of each job. I need a formula or macro to determine when the meeting date will be held? The criteria to determine the meeting date is found in rows 12-18. The end dates are set on different days of the weeks and different weeks of the month depending on the region that the job is located in. The meeting dates will be input in colums d, f, h & j.
View 7 Replies View RelatedI am looking for a formula to determine the oldest date in a range of cells. BUT, the part that's kicking me in the pants here, is that the range will change.
In column D, I have a person's name. The number of times that person's name will appear will change every time I run this formula. In column F, I have a date & time. I'm looking for a formula that will determine the oldest date/time associated with that person.
Im working on a document for work, its sort of a learning hobby tbh, something i want to learn. Im trying to do things myself from the books etc, vids from the net. This one i cant find the answer to so before i go ahead i would like to see if it can be done
Basically, we have a few documents that change every day. we get the raw data, and delete coloumns, change the size to autofit width etc...
(im creating a main macro to do that now)
But with that data i need to..
open a new book (but the book name is never the same... book1, book2 as you know)
paste the data
save it
.... the file name! needs to be something like this "Department 20130401" which i saved on 01/04/2013
if i did the document on 10th september.. it would be "Department 20131010"
I have Coloumn A with the dates 01/01/2013 etc
and Colounm B with Department 20130101 etc
Cell ("I1") has =Today() in it
could a macro change the filename from a vlookup result?
I have found an older thread that explains how to determine if a date falls in DST or not. See here Daylight Savings Time Funcitions. It does not follow the DST rules we use here in Canada and the formula is very confusing to me as it uses arrays, something I have yet to fully understand. DST in Canada begins the 2nd Sunday of March and ends on the 1st Sunday in November. I have a date in one column and a time in the next. What I would like to do is have a formula that checks to see if the date falls in DST and if so adds 1 hour to the second column. The result will show up in a third column.
View 4 Replies View RelatedI have a user form that has a calendar button. Once clicked it updates a txtbox on my user form. What I would also like is for another txtbox (txtQtrLeave) to be auto populated based on this entry. Below is the code I've tried:
If CalendarEnd.Calendar1.Value > #3/31/2008# And CalendarEnd.Calendar1.Value < #1/6/2008# Then
Qtr = 1
frmAddClient.txtQtrLeave.Value = Qtr
... Qtr 2, 3 etc
It seems to return a value if I have one criteria but it doesn't work once I add the And element. What am I doing wrong (this has been driving me mad all morning)?
I have written a few macros, which use date type variables. Because VBA uses the 1900 date system, and some workbooks use the 1904 date system, I have to first check and see if a workbook uses the 1904 date system, and if so, subtract off 1462 days where appropriate. My macros used to check the date system by using the command:
If ( ActiveWorkbook.Date1904 = True) Then
nh_stringToDate = nh_stringToDate - 1462
End If
But, sometimes the macro resides in one workbook (call it macro.xls) but the currently active workbook is some other workbook (something.xls). What I really care about is the date system of macro.xls and NOT something.xls. So, instead of "ActiveWorkbook" is there some other object I can use to refer to the workbook in which the macro itself resides? I don't want to rely on using the name of the macro workbook (macro.xls) because this could change! So I need a way to simply refer to the workbook in which the macro resides.
I have a database that holds thousands of names and the columns are of trinings that they have completed. I can filter by the criteria of name and of ID. ID number is unique so what I want is to know if Column F has a date in it once filtered. It maybe line 789 but that is all it shows because the filter is on.
View 2 Replies View RelatedI'm documenting the steps of a process with each step accounting for a specific amount of business days. How do I determine the specific business day based on the number of days it takes to complete a process? For example: Start date then 45 business days from that date the next step begins...then 3 business days from that date the following step begins and so on.
View 5 Replies View RelatedHow to create a formula to determine the longer period of time between two date calculations and enter the longer period of time in the cell.
My Spreadsheet:
Cell M2: Birthdate (entered in cell as 11/1/2004)
Cell J2: Last date of service (entered in cell as 10/31/2011)
Cell G2: Calculation to enter the longer period of time between (M2 (year) + 21) and (J2 (year) + 7)
Example using information from above:
2004 + 21 = 2025
2011 + 7 = 2018
2025 is the result I want added to G2
If there is any way to include the month/year in G2 that would be ideal.
File A has patients with discharge dates over two years. Each patient may have multiple dates. Example:
Patient Discharge Date
John Smith 7/1/2012
John Smith 10/1/2012
Judge Judy 7/1/2013
Judge Judy 12/1/2013
File B has office visit dates for the patients. Again, each patient has multiple office visits. I need to be able to see which office visits were within 15 days of a discharge date. Example:
Patient Office Visit
John Smith 6/1/2012
John Smith 6/15/2012
John Smith 7/10/2012
Judge Judy 7/20/2013
Judge Judy 12/12/2013
Is there any way to note next to the discharge date that for John Smith the 7/10/2012 office visit was within 15 days of the 7/1/2012 discharge? And the same for Judy with the 12/12/2013 visit?
I need to determine if the input date (Yearx) in a userform is a leap year. I tried doing this: Leap = Evaluate("MOD(Yearx, 4)") IF Leap = 0 then (show 29 days on my planner).
But no matter what date I put in, it generates "0" as the value for Leap, so indicates that February has 29 days. Obviously I'm not doing this right.
I'm needing a formula that will determine the number of days that fall in a specific month based on a date range. For example, if I have a date range of 10/15/2009 to 01/13/2009, I need the formula to determine the number of days in each month within the range (October has 15 days in the date range; November has 30, December has 31, and January has 13.) I have a large spreadsheet that would be so much easier to manage with such a formula. Currently, my spreadsheet is setup as follows. I need the forumla automatically fill in the number of days under each month.
Stard Date End Date Oct-09 Nov-09 Jan-10 Feb-10
10/15/2009 01/13/2009
I'm using Excel 2007.
I have a Userform that allows the user to input a country from a combobox which after clicking a button writes that data into Cell AH11 in the excel spreadsheet, what I need is for Cell AI11 to read what has been entered into Cell AH11, look up whether or not that country is on a list I have and enter a yes or a no if it is or it isn't on that list.
View 2 Replies View RelatedHow would I go about writing a loop that can run down rows until it hits, for ex 1/1/2010 (dates are in column A).
Also, would I be able to make a loop run for 30 rows and perform some calculations with two different columns?
I want to write a function that goes out and reads a cell, waits a few seconds, then reads the cell again, since it is constantly changing. Then report back the difference between the two readings of the same cell. The following code does not work since t0 and t1 always come back the same value.
Code:
Function ReadTime(t)
t0 = t
DoEvents
Application.wait Now() + TimeValue("00:00:15")
t1 = t
ReadTime = t1 - t0
End Function
I am working on a spreadsheet where individuals must enter a value between 1 - 4 or n/a. I would like to include a formula for each question where if they enter a value between 1-4, this scores 1 and if they enter n/a this scores 0. Does anyone know a simple formula I could use, I have tried IF formulas but keep getting muddled.
View 2 Replies View RelatedI have 10 min stock values that I need to sum by date (39 entries per day).
Because I have almost daily entries for 8 years, I need an automated formula to aggregate the daily data.
I tried using macro summing, however I could not figure out how to loop the macro to run for each day.