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 ????
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.
I have one monthly file which is in the attached format file name "Example".
I need the data from this file as per the format mentioned in the file "must be in this format".
from the "example.xlsx" file i need the login time (only first login) and Logoff time (only last logoff time) which is there in the description column. It must be available in the format mentioned in the file "must be in the format.xlsx".
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?
NameTime InTime OutAlan08300930Alan10001030Alan12301630Tony11301230Alan09450950Tony10301115
I would like to find the minimum time in and maximum time out for each person. The data type of Time In and Time Out are general.
I.E
NameTime InTime OutAlan08301630Tony10301230
Therefore, I would like to know what function in excel will enable me to perform such task. Furthermore, can this function use with VBA?
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 View RelatedI 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?
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.
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.
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.
Excel cannot complete this task with available resources. Choose less data or close other applications. My file is only about 3mb in size, made up of 17 worksheets. These sheets are calculated by referencing to another file that contains all of the background data. The data file is also about 3mb, made up of 13 worksheets.
There are probably about 2 - 3 thousand formulas in the file in total, ranging including vlookups, sumifs, sumproducts, etc. When the data was contained within the file there was no problem. I moved each data sheet into a new workbook to trim the size of my file and also stop the incessant calculation and this is when the problem started. Now, when I open the file and am prompted to update, it will update to about half way and then throw up the error message!
I need to work with a spreadsheet with 15000 rows of data. By the time I link this file with my final file and add formulas, my file has reached a massive size and the following pop up error message appears.
View 3 Replies View RelatedI 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).
View 4 Replies View RelatedIs 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.
I have a spreadsheet with date and time values of the format
"dd/mm/yyyy hh:mm" in column A followed by some other data in cells of that row.
What I'd like to do is have a macro that will delete a complete row if the time value in column A lies between two times that I can specify in the macro (the dates are irrelvant)
Excel 2007
Trying to insert a row and window popped out -Large Operation. " The operation you are about to perform effects a large number of cells and may take a significant amount of time to complete. Are you sure you want to continue?" I pressed ok , then, says "Excel cannot complete thsi task with available resources. Choose less data or close other applications. "
Consider this code:
'light eligibility
Dim facb As String
Dim sunset As Variant
[color=green]' check if facility has lights[color]
facb = WorksheetFunction.VLookup(RID, ds, 10, False) 'find facility code
If WorksheetFunction.VLookup(facb, fac, 6, False) = "Y" Then 'facility has lights
sunset = WorksheetFunction.VLookup(tempws.Range("A9"), sun, 2, False) 'lookup the sunset time based on the record's date
[Code] ......
This code checks the need for lights at a facility.
It first checks to see if the facility even has lights by cross-referencing a value in the record with a facilities database.
If it has lights, it then checks to see if they are needed. If the rental goes past the sunset time, then it needs lights. Sunset is determined by cross-referencing the date value in sheet1! A9, with the sunset database.
If it needs lights, variable lghtson is calculated equal to "sunset"-30 minutes.
As I step through this code:
WorksheetFunction.VLookup(facb, fac, 6, False) = "Y" Facility has lights.
Check to see if lights are needed.
sunset = WorksheetFunction.VLookup(tempws.Range("A9"), sun, 2, False)
sunset=0.879166666666667 which is 9:06PM. This is a proper value from the lookup.
If rental_end.value > sunset Then
rental_end (value from textbox) = "9:30 pm" , sunset=0.879166666666667. This is true, and Excel accepts it as true ...
lghtson = sunset - 0.5
0.379166666666667 = 0.879166666666667 - 0.5 (9:06 AM)
This is not the value I was looking for. I was looking for 8:34PM (0.856944444444444)
I am looking for a macros VBA where a user insert or update a data the date and time should be insert in column I and save the workbook.
Note: If the column I already have the date and time inserted before then it should give message record already have date and time.
I am using office 2010.
I have a workbook with macro button to run some vba, if i want the button to disable when the user make copies of the workbook (date created is later than original wb saved time) what codes to add in the VBA?
what i assume is to add the time to a cell when the wb is saved, when the wb opens, it check for that cell if the same with the date created, if different, then disable the macro button.
I have a column of values resulting from subtracting a static date and time from the current date and time.
This means it is constantly updating, which makes it impossible to sort.
All my work depends on sorting those values, though.
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.
View 2 Replies View RelatedI have a time column (A) that when looked in the cell only shows AM & PM times, but the cell itself (not showing) contains dates too, keeping me from be able to do a sheet wide sort of time or time frame occurrences.
Can I do some thing to sort these cells with their corresponding rows based on time only disregarding dates?
I am trying sort out all rows that in column (A) is time equal to or greater than 4:00 PM OR even maybe sort all rows that column (A) shows a time between 4:00 PM & 7:00 PM. The date in the cell is the problem, I think. Excel 2013
I have a spreadsheet that users update which I then want them to perform a number of checks on. At the bottom will be a button for them to click stating "Click here to confirm checks completed". When they click it I want it to put a flag somewhere that I can then use to prevent them from saving if they haven't clicked the button. I know how to do the saving but, but how do I get the button to put a flag somewhere?
View 2 Replies View RelatedI'm doing a study of when people are most productful in their day. I just need to count how many units a person made within say 4 hours of starting work.
My data is similar to the excel I've attached
Count completes.xlsx
I'm using Excel 2010 too.
I'm trying to look up information in "pi" by entering a time that you want to look up say 1800 or 935 and have a cell that would enter it as todays date with that time so I can use it as a timestamp in "pi"
View 1 Replies View RelatedI have one column with as many as 50,000 or more rows. The data format for each row/ cell is unique as shown below ( date and time). I wanted to split the data as shown in "Formatted Data" below. Have Tried Text To Column formatting but didn't work right.
Raw Data: Formatted Data (2 cells):
2005/11/02 23:55:15.758 ==> 2005/11/02 23:55:15.758
2005/11/02 23:58:16.698 ==> 2005/11/02 23:58:16.698
2005/11/03 00:07:13.830
2005/11/03 00:10:14.971
I have VBA code that attempts to delete an entire row from my worksheet:
Code:
Cells(3,1).Select
Selection.EntireRow.Delete
This works fine on small data sets, but on larger data sets it gives me the error message, "Excel cannot complete this task with available resources". This happens even when I try to do the deletion manually (without VBA code). Clearly, the code itself is not the problem.
My document has about 250,000 rows and 2,500 columns. While this is big, it is significantly smaller than Excel's documented limit of 1,048,576 rows and 16,384 columns.
I am using Excel 2007. My computer has 2GB of RAM but even when I try it on a computer which has 8GB of RAM it gives me the same error. If I "ClearContents" instead of "Delete" it works fine. For my purposes, however, deletion is entirely necessary.
I would like to merge a colum with a date and a column with a time into one to show DD/MM/YYYY HH:MM
I have tried merge cells and it takes out the time and leaves the date only.
I have a cell with both date & time "10/9/09 3:15" This is put in the current cell by formula which indexes two dif. cells, Now I am trying to copy this cell and paste into another book but like to have only date. How can I do that? Each time I try it gives me the time value in the pasted cell and I cannot even format it.
View 5 Replies View Related