I have cobbled together some code that searches a folder of my choice and lists the files within the folder every xxx seconds for yyy minutes. The files it finds are listed in 'Column A', and between each search during the defined yyy search duration 1 row is added to seperate them.
I require the additional code to record the search time in 'Column B' of the corresponding Row, and the search date in 'Column C' of the corresponding row. The Time & Date will simply be the Computers Time & Date at the occurrence of the relevant Search interval. The code is as follows:
I seek advice on using the value of NOW() as a record ID in an address book program. Question #1: Do Excel developers often use a record ID? Question #2: What record ID schemes are fequently employed besides date/time? I have decided to create an Excel address book as an exercise to increase my knowledge of VBA, and also as a useful application for work.
I realize that a record ID is not essential in Excel in the way that it is essential in Access, but I feel the need to have some unique ID associated with each address, so that I may have different worksheets, with data related to a given Contact, sort and manipulate it, if necessary, but have the record ID as a way to restore the relationship of rows to a given Contact, and also, as a handy way to examine the data in the date/time sequence in which it was entered. I have experimented with the following code, to assure myself that I can access the number returned by the NOW() function, manipulate it as a string, and format in various ways if necessary.
Dim n As Double n = Now() sn = Str(n) p = InStr(sn, ".") first = Left(sn, (p - 1)) l = Len(sn) d = l - p S = Mid(sn, (p + 1), d)....................
I have already looked at many of the posts but I have not found the solution that I am looking for. I am familiar with VB and the NOW function.
I am trying to create a macro that will reference a cell and place a static date and time next to the cell. I want this date and time to only change when the checkbox is clicked.
I have a checkbox in E11 that is linked to F11, I would like to have G11 record the date and time that the checkbox is clicked. I have already tried the following.
I'm having trouble while trying to use a variable in a cell reference. I have a sheet that is being used to record the time and date of data entered into the workbook. This sheet is divided into columns, each designated to a specific type of data. I have written a macro to enter the current time in the correct column, but that macro is embarrassingly long and complicated because I used a series of if statements to handle all the different data being entered. I'm trying to simplify by using vlookup, and assigning the column value to a variable, and inserting that variable into the cell reference.
Here is the line I need to insert a variable into:
I ran a Home Service Massage, with ten massagers and it's continually growing. I know I'm supposed to use a database for this, but I'm not very good in creating one. I am however confident in using MS Excel, so in a much as possible, I only want to use MS Excel, and strictly use formulas (if I can get away with it).
Anyway, I keep customer records in one tab, and all their personal info is listed there. There are two types of columns, which I'd like to call 'static' and 'automatic'.
Static columns includes Unique ID (number), Name, Address, Phone Number, etc.
Automatic columns, by the term I used, is filled automatically. This includes the total number of hours they have availed the massage from us, the names of massagers who serviced them, --- and my biggest issue --- the date of last massage. I can manually input this, but I normally forget, so I want this filled automatically.
Aside from customer tab, I have one tab for each massager. First column is the date, then 48 columns for time. Starting from 8AM, 830AM, 9AM... 730AM. Hence I have 48 of them. Each column is equals to 30mins of massage availed. I copy and paste the Unique ID from customer tab.
so A2:A32 are dates, and B2:BC50 is where I copy paste the unique ID. B1:BC1 are time labes.
Is it possible for me, just by using formulas, to populate the 'last massage date' column?
I am creating an asset management sheet. For the formula I am trying to work out there uses 3 fields : ID, start date, and end date.
What I want to do is be able to show if the ID is duplicated within another record with an overlapping date. So an item is flagged if it is in the list within the same dates as another record. I tried a few countif formulas but with no success.. I may just be approaching the problem incorrectly though.
When a cell changes i would like the cell in the next column to record the date of this change. After achieving this i want to set up conditional formatting to compare this date to the current date and if they are the same then change the original cell's colour to show it changed today!
I started using the code below (from ozgrid) which was working fine BUT my cell value in column A is actually taken from a different column. when the different column cell value changes my cell in A changes but the script below does not seem to see this as a change. it only records the date if i physcially change the cell in A rather than another cell prompting it to change.
based on my overall objective and my plans to date please can someone suggest a solution? current code below....
I wrote a formula to recognise when a child equalled or bettered a club record (CR) in an event. However what I neglected to do and cannot work out is for the formula to recognise the new time as the new club record that will then have to be equalled or bettered from then on to be recognised as a club record. For example CR for 70 metres is 9.28 secs. Formula I was working with was IF(ISBLANK(B3),""),IF(B3
i have a spreadsheet with different customer names. I want to highlight a row every time a customer name changes. For example: ABC, DEF , i want to highlight where abc ends . Normally i use subtotals, then group them by rows then select visible cells then i highlight every different customer. The problem is that since it is a huge sheet, it takes 30 minutes to do the subtotals. I am not sure if conditional formatting can do this.
What I am trying to do is record the time when a task is completed on the spreadsheet. For explanation sake, lets say I have list of 10 tasks in column C 1-10. In column B 1-10 I have a button for each task. When you press the button for the task, the time it was pressed is recorded in D 1-10. To put the time in I used =now(). The problem that I am having is, when you press button 2-10, it changes the time in the cell above to the same time. So instead of have 10 different times in 1-10, I have 10 identical times,being the last button I pressed. I tried timestamp, time value, etc
What i would like to do is record a cell on sheet1 in sheet2 when it reaches a certain number i.e 2. The cell in sheet1 changes all the time but sheet2 must not refresh. Is this possible with a formula?
I am attempting to pick up a date with time entry on a worksheet and place it into a TextBox on a UserForm. Format on the sheet is mm/dd/yyyy h:mm AM/PM. The UserForm is placing the value as mm/dd/yyyy 12:00 AM. here is the
Private Sub UserForm_Initialize() If Not Range("dDate").Value = "" Then TextBox2.Value = Range("dDate").Value TextBox2.Text = Format(DateValue(TextBox2.Text), "mm/dd/yy h:mm AM/PM") Else TextBox2.Value = "" TextBox2.SetFocus End If End Sub
"dDate" is the named range where the date is sitting. The format is also set on the TextBox2 exit event. Can anyone see why only the date portion is being transfered with the default 12:00 AM for no time component of the value?
I have loaded a .csv file in which the first column contains date/times, e.g. 01/12/2012 00:00. How do I now tell Excel (2010) that this is in fact a date/time format? If I select one or more of the cells, click on the Number dialog box launcher and try to pick a suitable format tghe cells resolutely refuse to budge from being text (i.e. left-justified, still allows me to edit the 'seconds' component to a number > 60). Also which data type should I be using? The only one that appears to have a full date/time format listed is Custom (not Date or Time).
I know this is a simple question for all VBA droids...I guess the challenge is in how quickly I'll receive an answer. I hear the turn-around time on this board is pretty impressive. So here goes...
What is the VBA code for recording the current date without the date recalculating when the form is re-opened. I know the formula for automatic entry [=today()], but I need the date to stay the same. I can assign the date to any cell, so you may theorize any cell in the code. Remember...the trick is getting the date to stay the same once it's been automatically entered.
i am building a data base and every day, say, at 5pm a certain cell should contain a corresponding date. In my macro I simply wrote
Range("A1").Offset(i-1,0).Formula="=Today()"
I thought naiivly it would work, but it doesn't, as the next day the date will be changed correspondingly. So, the question is: how to record todays' date (Dec 29) so that this date stays in this cell forever? Only the date, not Excel Dates & Times
We have multiple work orders per opportunity, and each work order has a date. I have a sheet of these work orders which shows the work order #, work order date, and opportunity date. What I want to do is add a column which reports the work order date if it is the earliest instance of a work order for the opportunity (said differently, the first work order for the opportunity). Is there a way to write a formula to report whether the date of the row is the earliest date for a given opportunity, all in the same table?
I have two sheets one = where I am entering day to day data two = For getting result
Once I entered data in first sheet then I want date wise record in another sheet in which I will enter date. After entering date all the record will be inserted in another sheet from first sheet And main sheet will remain with all records
Is there an event that fires every time anything is clicked within a userform? I have seen the userform_Click() event however, this only works if the userform is clicked directly and not if something like an image is clicked on top of it.
I import data from a program that exports dates and times as text. I have been successful using "text to columns" to separate the time from the date and then using =text(A1,"00:00")+0 to get the time to show as serial time but I'd love to be able to do the whole date/time string in one step. In cell A1 there is data that is general format and is in this format:
01/01/13 00001
No matter how you try to format it, it is not a date or time. For this project I need the serial number for the date/time. Any formula that will format it as date/time and then allow it to show as a serial date/time?
I have 04/02/08 12:00:01 AM (mm/dd/yy hh:mm:ss AM/PM) in text format in a cell. I need to convert this to date/time custom format as given above so that I can make comparisons with NOW() output.
I have an excel file with the ID, Name, workID and Creation date fields. Some people have several work ID's created in different times. How can I select row that contain work ID's that were created the latest. I may have several records per person, I need only the latest record. File contains about 3000 records, I can't go through all of them.
I have this table as my simple little database and I have to extract some info from it and keep track of dates and deadlines...
A B C D E F G H
[Code]...
I have to get the following data, I need to lookup the last visit date out of all dates associated with a specific name in column "A".
This requires that I test an unsorted table for a value in column "A", i.e. "test2". Then determine which record has the latest date.
In other words, If I test for the value "test2" in column "A", I find I have 2 records, with dates ranged across "D4:F4 and D7:F7".
I need to return the latest date found in those ranges...
The purpose here is to write a function that will find a range of dates associated with a name in an unsorted table, from that range find the latest date, then add 90 days to that date and display the results....
I have been trying to employ a form of, =VLOOKUP(E3,$B$3:$D$11,MATCH($F$1,$B$1:$D$1,0),FALSE) and =INDEX(Table_Array,MATCH(Lookup_Value,Lookup_Array,0),Col_Index_Num) combined with "IF" functions, but to be totally honest I am COMPLETELY lost here....
When I paste data in Sheet2 in the attached CF Example2 file (Just by Selecting A1:L18 Cutting it and Pasting in the same place), the action of the VBA code in the Thisworkbook is correct but I am getting the 1004 Error Message from the VBA debugger saying "Run-time Error '1004': Application-defined or object-defined error
I am using Excel 2013. Anyway, the first issue is that I need to pull a date and a time period from text. So, for example, if I see something like Sunday Prime Time 7/6/14 8:37PM, I would want to pull ONLY the "7/6/14 8:37PM" out of it. Each text box could potentially be different, so it might not always be in the same format as "Sunday Prime Time 7/6/14 8:37PM" it might only show just the date and/or the time without all the extra text i.e. 7/6/14 8:37PM. Some of the cells will have text, others might only have just the time or even just the date and the time. The only thing that I am worrying about in each cell is extracting just the date and time. If this is too much to ask of excel, I would be ok with extracting ONLY the time - 8:37PM and not the date, but I would much rather be able to get both the time and date.
THEN, onto part two of my question. After I would pull the dates and times, I need to compare them with each other. So, when I have the same date with two separate times on that date, I need to write a formula to show if those times on that date are less than 30 minutes apart. So, if I have 6 times on 7/6/14, I need to know if any of them are less than 30 minutes apart.
I would need to have the formula say something like "Problem" if the times on 7/6/14 would be 5:30PM, 5:48PM, 7:00PM, 8:00PM, 8:15PM, and 9:00Pm for example. I would like to see the word "Problem" since 5:30PM and 5:48Pm is only 18 minutes apart, and "Problem" after 8:15PM since that is only 15 minutes past the 8:00PM which is obviously under 30 minutes. The times that are more than 30 minutes apart such as 7:00PM and 9:00PM for example are more than 30 minutes apart from any of the other times that were extracted.
