I am working on a system where the system is having "australia time". Now, I want a worksheet where it should reflect the India Time after pressing a specific key in a specific cell.
View 3 RepliesI am looking for a formula to change a condition based on the time of day. Essentially, before 1pm I would like the formula to be:
=((I12/100)*25)/D12
After 1pm I would like the formula to be
=((I12/100)*25)/B12
I tried various ways with "=if(now()> ..." formulas to no avail because of the way Excel handles Now().
I got Excel for Mac 2011 installed. I have a table called 'Raw" that holds data from the beginning until the end of 2013 (in total ~7000 rows). I have columns for Category, Start and End time, and time period in Hours, Minutes, and Seconds:
Code:
A
B
C
D
E
F
Category
Start
End
Hours
Minutes
Seconds
Transportation
2013/01/01 01:00:00
2013/01/01 01:15:00
0:15:00
15:00
900
[Code] ...........
I'm trying to get an overview of how much time I've spent in January on Transportation. I would need to look at the Category entries for Transportation, then only use the ones which have the Start set to 2013/01, and then take the Hours/Minutes/Seconds, and summarise it in a new table called 'Data':
Code:
A
B
C
D
E
F
Month
Category
Occurrence
Hours
Minutes
Seconds
2013/01
Transportation
48
[Code] .......
I have figured out a way to count the occurrences for Transportation for January:
=COUNTIFS(Raw!B:B,">="&"2013/01/01 00:00:00"+0,Raw!B:B,"
A vehicle has been assigned to collection, time of entry appears in Load cell coresponding with vehicle eg.e vehicle 1 'A1' = load 1 'B1'. I can get the exact time to appear in the cell currently but when I try to assign a second truck, the load 2 'B2' cell AND the load 1 'B1'. Im sure that there is an easy way to do this but i just cant figure it out. Can someone please help me out. Can i disable the cell that is updating?
Rather than manually typing the time in 5 min increments across a range of cells, i want to have a start time, then the next cell add 5 mins. EG: 09:00 next cell 09:05 and so on.
View 7 Replies View RelatedIs it possible to launch Excel at a specified time without someone pressing a button?
I'm working on a network which has disabled Scheduled Tasks. The purpose of this request is to launch Excel in the early hours of the day so that a macro would run and update the data from a data source. The macro works brilliantly, but I without anyone in the office at 2am to open Excel, I need to find a way to launch it automatically.
Also, I don't have permissions to install programs or add-ins from the Internet. It doesn't leave me with many options, but I'm just wondering if there's a trick that has so far eluded me.
I want to run a macro at a specific time and day. For exemple the macro needs to be run every Friday at noon.
Also, I don't want to worry about opening the file all the time.
I would like to create an average function that will take an average of the Column labeled "Gap Time (Hours/Min/Sec)". I only want it to take the average for this for each new start date. These values will change daily so I was hoping that someone may help me write a function that will work when data changes instead of manually taking the average every time data is entered.
View 5 Replies View Relatedto open a workbook at a specific time. I want to beable to flip my computer on at 8:50am, go grab some coffee and then at 9:00:00am have my workbook open and performing tasks. Besides turning on my computer, I dont want to have to open or activate anything prior to having my workbook open. However if there is code to turn on my computer from a sedative state that would be great too.
View 9 Replies View RelatedI want to know if I can schedule a macro to run at specific times?
If so would 6 requests at the same time slow excel down?
I am looking to calculate the time difference between 2 date time fields using a specific calendar.
I work in an engineering company and am having problems calculating machine utilization efficiencies.
Example:
Job 1: Start date 06/01/2009 10:00:00
Job 1: End date 06/02/2009 12:00:00
Calendar:
Dayshift: Monday - Thursday 07:15:00 - 16:15:00, Friday 07:15:00 - 12:15:00
Nightshift: Monday - Wednesday 21:00:00 - 07:45:00, Thursday 21:00:00 - 05:45:00
Using the specified calendar I am trying to calculate the time difference between the start and end date of job 1.
Is there a way i can convert the time to a specific number? for Eg. If incell A1 i have the time in following format 01:15:08 (MM:SS:MS)
i want the result in A2 to show 7508
01 minute = 60 seconds
Plus 15sec = 60 + 15 = 75
and 08 miliseconds would give 7508 ??
I have a database of over 10,000 entries. I am trying to get the average time for four specific entries (department, test 1, test 2, test 3). I did an averageifs for the tests individually and calculated the average time individually.
The three tests comprise of the department.
Average of test 1 = 40.8
Average of test 2 = 39.9
Average of test 3 = 94.8
Average of dept using the averageifs is 41.3
Average of dept by taking the average of the values above is 58.8
Why is that discrepancy there?
Need to have a particular workbook open at a specific time...
I wrote this and placed it in a Personal.xls module, did not run automatically. I then placed the 2nd SUB() in the "This Workbook" and nothing. Where or how is the proper way to run this...
----------------------------
Sub Open_IndexAnalysis()
Workbooks.Open Filename:="e:Index Analysis.xls"
End Sub
------------------------------
Sub Run_OpenIndexAnalysis()
Application.OnTime TimeValue("09:40:00"), "Open_IndexAnalysis"
End Sub
-----------------------
I have a module which runs behind a command button which creates a table and outputs it to an excel workbook. Is it possible to cause this module to execute at a specified time, say 9.00 pm or when the database is closed. It is not necessarily the case that the database is closed every day.
View 9 Replies View RelatedI can open a file at specific time using TaskSchedule. How do I close it at specific time?
I am trying to write a simple one-line code for that but do not know how to combine two statements:
Application .OnTime TimeValue("17:01:00")
and
ActiveWorkbook.Close
Also, this workbook is updated every 1 minute using the same OnTime method and everything works perfectly. However, when I try to close file there is a message saying that "OnTime method object application failed" and I have to click "Debug" or "End".
Only when I click "End", the file is closed.
I am trying to identify repeat customers with a view to removing the purchase if the customer buys the same product within 5 days.
If the same customer buys a different product each day then I would like to count each purchase.
If the same customer buys the same product every day I would only like to count it once every 5 days.
I have tried using COUNTIFS for this. Formula in column D where A contains the Customer, B the Purchase Date and C the Product:
Formula:
I'm trying to extract some data from an online page, but I require a specific cell at an exact time, each day.
For example, I would like to acquire the main data from the following page: [URL]
into excel, but additionally, I require the ESU14 (Sep '14) Open cell at exactly 0700 BST. I am currently using the 'Data' - 'From Web' feature to scrape the necessary fields into Excel however I haven't found a way to acquire one of those cells, at exactly a certain time, every day, even when Excel et al aren't open.
Looking for code that auto updates today's date in column C, when cells in either column a or b of the same row are edited or changed. I have never used VBA before, just browsing forums for something that might work. I think I'm close with this one, but maybe the offset is wrong - it populates the date in column b when a change.
View 2 Replies View RelatedI have a output file that is collected time stamped data. How do I select the rows of the first time stamped collection. So, if the first row is stamp with 03-Jan-2009 23:59:00, I want to copy all the rows with that time stamp below the first row and stop when the time stamp starts at 03-Jan-2009 00:00:00. Attached is my sample sheet.
View 4 Replies View RelatedHow many time each item appear in specific range.
For example:
apple
banana
grape
apple
mangosten
orange
banana
banana
mangosten
In that list we should get :
apple=2
banana=3
grape=1
apple=2
mangosten=2
orange=1
banana=3
banana=3
mangosten=2
If I am using =COUNTIF(F6:F14,"apple") then I can get the result of apple.. and just keep changing underlined word but if then i have so many data, i cant do that.
I am trying to get conditional formatting to work on this problem but I am getting nowhere fast,
In A1 I have a start date, I want B1 to turn Green if A1 is less that 1 year old, C1 to turn Yellow if A1 is between 1 - 3 years old and D1 to turn red if A1 is more then 3 years old.
I am attempting to copy a range from one worksheet and paste it to a new worksheet when the system clock hits a specific time. It needs to run continuously and identify that a previous colum has been filled and paste into the next open column. I am just getting my feet wet. I have some code to begin with but I am getting a 1004 error.
'Application. ScreenUpdating = False
Sheets("mysheet").Select
Range("E3:E10").Select
Selection.Copy
Sheets("CmyOtherSheet").Select
Range("B1:B8").Select
ActiveSheet.PasteSpecial xlPasteValues
'rTime = TimeValue("12:31:00")
'Application .OnTime rTime, "RunThisProcedure"
i have a timesheet that we are trying to use. the problem is the column that says shift diff. if an employee works after 6:30pm for 1and 1/2hr, he is entitled to shift hours. shift hours is between 6m and 8am.
As long as he works after 6.30pm but works for at least one and a half hour, he will get the shift.
if work, 9am to 7:30pm, and have break between 2-3pm, should have 1.5hrs shift and 9.5hrs total
if work, 7:45pm-9:45pm, and have break between 8:30-9pm, total hrs work is 1.5 and shift hrs s/b 1.5hrs
if work 3pm to 12am and have break between 7-8pm, total hrs work is 8 and shift hrs s/b 5hrs
I am trying to pull a specific time frame of data from worksheet, in a large file, into another active workbook. A fiscal month. I don't know how to at all. I figure it should be a And IF and Vlookup but do not know how to execute it all.
View 1 Replies View RelatedI have a delivery time programm (clients to be delivered the next day) which I would like to run every night at a specific time. Is there any MS tool to trigger the Excel file? Or do we have to use the "old" batch file ( if yes, pls send example)?
View 2 Replies View RelatedI have a lot of rows of data in this date and time format "2/29/2013 10:00:25 AM" and I would like to remove only the time and leave the date
View 2 Replies View RelatedI have got a spreadsheet which is being used as a database. I would like the spreadsheet to close automatically if the user has not "touched" it for a specific amount of time (say 1hr).
2 scenarios:
a/ The user is working in Excel but with another workbook.
b/ The user has the excel spreadsheet open but is working with another application, say Word
I experimented with Workbook_WindowDeactivate but I do not know how to stop the process if the Workbook is reactivated (Maybe a DO ...LOOP calling a function returning a True/False statement on the event Workbook_WindowActivate !!). My way of thinking is the following
Workbook is deactivated
Start a timer
If the workbook is not reactivated with 1hr, save and close (no user intervention wanted)
Else stop timer
Repeat process
i need to put a date stamp when a change is made in b3:b31 into e3:e31 for each row also i need to put a date stamp into g3:g31 when a change is made in F3:f31 i try to use 1 "worksheet-change" and it is fine once i use 2 i get Ambiguous Name Detected errors
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("b3:b31"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 3).ClearContents
Else
With .Offset(0, 3) ............
The spreadsheet has over 200K rows and two dozen columns. My job is to:
1. There are two columns, one is called OCR code where we have repeating ID's and other column is called Minutes Count which is basically a number. I have to filter the OCR column for similar OCR codes(values) and then add their time(Minute Count) so that I get total minutes for a specific OCR code. After adding the min I will copy them into a new sheet with the code in one column, total minutes in another and number of repeatitions for that specific OCR code or count number in a separate column. Example:
OCR_Code; Minutes
54xg; 456
45fk; 65
23IS; 18
54xg; 971
45fk; 265
.
.
.
Now I've to filter 54xg so that I can only see it and its corresponding minutes. Then I will add up all min for 54xg. Then I will count the number of instances a 54xg has been repeated in spreadsheet(here its 2 but it can be in hundreds in my case). Last thing is to transfer all this data to a new sheet. And automatically repeat the process for other OCR codes like 45fk, 23IS etc...