Record Time When Task Was Completed?
Jan 23, 2014
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
View 1 Replies
ADVERTISEMENT
May 25, 2007
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)....................
View 2 Replies
View Related
Jun 5, 2012
What I am looking to do is find the time it has taking to complete a task - So the work sheet has four cells (See below) and return the total time with cell E
Cell A Cell B Cell C Cell D Cell E
June 5 10:00 AM June 6 12:30PM ????
View 2 Replies
View Related
Mar 18, 2007
I have a sheet to calculate Time Elapsed based on StartDT & EndDt taking into consideration the actual working hours in any working day and the function exclude the weekend. I am not a guru in excel but i found this formula in this website .
=IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT(StartDT),HolidayList,0)))),0,ABS(IF(INT(StartDT)=INT(EndDT),ROUND(24*(EndDT-StartDT),2),
(24*(DayEnd-DayStart)*
(MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+
INT(24*(((EndDT-INT(EndDT))-
(StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+
MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+
(24*DayEnd-(24*(StartDT-INT(StartDT)))),2),
ROUND((24*(DayEnd-DayStart)),2))))))
the formula works perfectly except for the fact that in our part of the world we have "Friday & Saturday" as the weekend days instead of Saturday and Sunday.
is there a way to get this formula or any similar formula to take different weekend days and do exactly what i need?
View 9 Replies
View Related
May 22, 2014
Given:
6:21
3:04
2:29
0:48
These represent time it takes for a fax to send. I'm trying to find the average time it takes for these faxes to send. Using the built in @AVERAGE option does produce a number albeit an incorrect number. Wondered if I could convert these to decimal numbers then covert the result back to time - when trying that I don't get an average but rather a time of day - say 5:55:30PM.
View 2 Replies
View Related
Feb 1, 2008
Currently I have 5 identical command buttons which do something similar but in a different cell.
What i want to do is sum up the 5 different task using a single command button.
When cmdaddsp1 is clicked the first time cell "B4" is populated with a value from a table.
When cmdaddsp1 is clicked a second time cell "B5" is to be populated with a value from a table.
When cmdaddsp1 is clicked a third time cell "B6" is to be populated from the table,
and so forth.
Is there a loop or anything that can be used for this? I only want other cells to be populated when the command button is pressed.
View 9 Replies
View Related
Oct 15, 2007
The below is for Excel 2003.
I want to set up a spreadsheet that can show my work is completed within a service level agreement, based on working hours of 9-5 Monday-Saturday.
I want to be able, for example, to log that a piece of work is reported at 09:00 on a Monday and completed at 10:00 on Monday and for excel to calculate that as 1 hour until completion. Fine so far. But what if that job was closed at 10:00 two days later? Based on an 8 hour working day, that should be 17 hours.
And what if a bit of work comes in at 17:00 on the Saturday. No one works the Sunday. Say the job is completed at 10:00 on the Monday. that should calculate as 1 hour to completion.
The SLA I'm setting is 4 hours.
View 9 Replies
View Related
Jul 9, 2008
I get a spreadsheet downloaded to excel with a variable of 15 names.
I want to create a formula that if the name Cleardale appears to take the time responding in cell BD and subtract it from the time available in cell AV so that I have the time on task and then to add up all those times in a total time on task for all cleardale listings for that day. The times are listed in the 24 hour format. I keep getting errors. Can anyone help me out on this one? The range of the cells is from AV7 to AV500 for available time and BD7 to BD500 for alert time and the range of the names is cell f7 to f500.
View 9 Replies
View Related
Dec 8, 2013
Is it possible to create a drop down menu where additional information can be added then tallied up in a separate table?
I would like to create a menu representing "tasks" where an amount of "time" can be designated per menu for my employees to select and fill. I would like this information to then be tallied elsewhere so I can keep track of our efficiency.
I've attached the excel file to give a better idea of what I'm trying to accomplish.
View 9 Replies
View Related
Jan 7, 2012
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.
View 6 Replies
View Related
Mar 9, 2009
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:
View 4 Replies
View Related
Jul 1, 2009
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.
View 14 Replies
View Related
Oct 14, 2007
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?
View 9 Replies
View Related
Jun 17, 2009
I created this macro (below) using the record function but when I kick it off it takes 40 seconds or more to run.
View 4 Replies
View Related
Feb 24, 2012
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:
Sheets("Current").Cells(Rows.Count, columnstart).End(xlUp).Offset(1, 0).Value = Now()
Where columnstart is my variable. It is an integer, but it's value will change depending on the type of data being entered.
View 7 Replies
View Related
Aug 14, 2013
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?
View 2 Replies
View Related
Mar 16, 2014
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.
View 1 Replies
View Related
Jun 18, 2009
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....
View 9 Replies
View Related
May 26, 2009
I am looking to create a macro that will create a new sheet when data is added on a summary sheet. Example.
1. Summary sheet called "Variations" contains columns that will contain the information needed for new sheet (Columns A to D)
2. When data is entered on "Variations" sheet: Column B, then macro automatically creates new sheet renamed to e.g. VO1 (Number used on "Variations" tab) and is a copy of "Master" tab.
3. Data entered in Column A to D on "Variations" tab is automatically entered onto new sheet created (e.g VO1). Shown is blue on attached file. Additional data is updated on "VO1" sheet and this then links back to "Variations" tab
View 6 Replies
View Related
Mar 20, 2009
I'm trying to find a macro that will run allowing the user to select a 'starting record number' and a 'finishing record number' when printing.
I have a spreadsheet that feeds from a master list in excel, from over 5000 records.
I need to print the s'sheet with any given indivdual record's information at any given time.
Individual prints are fine. However if I wanted to print from record number 1500 to record number 3000 it would take me all day.
Is there a way I can set up a macro so an option form pops up? allowing selection of "From record" and "to Record" ?
View 13 Replies
View Related
Oct 6, 2006
I have a large workbook with hundreds of rows of data.
I have sorted the data and ended up with product codes in column A and my data between columns C and V.
I want to do a sumif with a difference if that is possible.
I want to sum all of the data in column C based on the product code in Column A. But I don't want to include any rows of data where Column V is blank.
i.e. If my sumif for a product code covered 3 rows C3:V5
I want to sumif Column C dependent on the product code in column A but I only want to include rows where there is a value in Column V.
So if all of the cells between C3:V3 contain data include count C3.
Moving down to row 4 if this row only has data between C4:M4 don't include C4.
Moving down to row 5 if this row has data C5:V5 include C5 in the sumif.
i.e if there was 100 in C3, 200 in C4 and 300 in C5 the sumif would be 400 because although C4 has a value V4 does not.
I want to roll this out across all of the columns C:V using the same method if possible.
View 7 Replies
View Related
Jul 12, 2006
We have a spreadsheet in use at the moment which has various lines of information, each line has a customer name, id, request and various other bits of information which needs looking into.
The infomation is arranged onto weekly sheets and is managed manually. Once the customer request is complete, we grey out the background colour. Some outstanding issues background colours are either left blank, yellow or red, depending on whos doing them.
I was wondering if there is a search criteria that we could use that would search through all the sheets in the workbook and find any customer queries that are not greyed out and display them on the results page.
View 8 Replies
View Related
Jul 9, 2013
I have the following setup on a worksheet :
5427 Data
5427 Data
5427 Data
6565 Data
6565 Data
7765 Data
all I want to do is have the above table look like this :
5427 Data
6565 Data
7765 Data
The concept being that I can double click that row to see the additional information(i.e. expand to show extra information)
Real life use would be of such, the first column contains the invoice number, the 2nd 3rd etc contain item associated with such invoice.In grouping the column invoice number, you only have to look at one invoice, instead of it duplicating for each item contained within the invoice.
This will need to be done in an efficient manner(over 4000 rows need grouped)
I realize I could just create a list that has one copy of the duplicates, but I want to extract the original information as well. For example, it would look like the following :
Original :
1212 Data
1212 Data 2
1212 Data 3
2323 Data 1
2323 Data 2
Edited :
1212
2323
double click 1212 for :
1212
+Data 1
+Data 2
+Data 3
2323
Note : This grouping will also have to span across the whole row, sample rows :
Invoice# Price Color Weight Item
1212 Column1Data Column2Data Column3Data Column4Data etc.
1212 Column1Data Column2Data Column3Data Item2Column4Data etc.
View 4 Replies
View Related
Aug 11, 2012
I am trying to come up with a composite score that if a child takes a particular course and has a grade then make a composite score. Here is an example below.
ABCDEFGHIJK1Math CourseScience CourseReading CourseWriting Course
MathScienceReadingWriting Composite Score Looking for2APGEAPAP A+BC-B- 11003
In the example below I am looking for a composite score of 1100. Here is how I derived at it!
=4*100 + 3.2 * 50 + 2.4 * 100 + 3.0 * 100 = 1100
Code:
Function composite(r As Range, course As Range) As String
Dim d As Double
Dim score As Double
[Code]....
View 7 Replies
View Related
Aug 27, 2007
I need to be able to have users complete a range of mandatory cells before they are able to close a workbook.
Foe example cells c5,c10,i11, i18,i22 i24,i26 etc etc need to have data in them before uses is allowed to save or close the workbook. I would like to have a message box prompt to alert them to the issue.
View 9 Replies
View Related
Aug 23, 2006
I have a little problem and could use some help please.I created a worksheet for logging orders but now find it too large to cut and paste but have little knowledge of macros
I would like to have any completed rows moved to another sheet in the same workbook ...
View 9 Replies
View Related
Jun 25, 2008
This seems easy in theory but it's above my head. I have written code to add text and numbers from a userform to certain cells on a worksheet. Now, is it possible to display a message box after the code executes to acknowledge the code has executed successfully? I have tried simply adding the Msgbox function at the end of the code but that does not work.
View 2 Replies
View Related
Mar 3, 2013
I am maintaining a spreadsheet to enter all my sales orders. I need a code which automatically moves the entire row to a new sheet "Completed" when the order status is selected as "Completed".
View 6 Replies
View Related
Jan 27, 2014
I've got a password protected spreadsheet with only certain cells unlocked to allow users to complete them. I need the following to work on Excel versions 2003, 2007 & 2010.
Cell E3 has a drop down list with types of car to choose from. One of the car types is "Pool_Car".
Cell D12 should only be used if "Pool_Car" is selected from the drop down list. If any other car type is selected, and the user tries to enter a figure in D12, I need an error message to appear.
E3 & D12 are unlocked cells.
I'm happy for D12 to become locked & only allow it to be unlocked if "pool_car" is selected, but I've looked elsewhere, but can't find VBA or data validation to work.
View 3 Replies
View Related
Dec 15, 2008
I'm trying to run a macro that only shows completed projects, which means all elements of a project have been labeled "Completed". See example below; 1868 is the only project I want to see when I run the macro, 1869 and 1870 are fully complete, so I don't want to see them.
Project #Status1868Complete1868Complete1868Complete1869In Process1869Complete1870In Process
View 11 Replies
View Related
