On Time Function
Jan 5, 2007
I finally got an ontime code to work almost the way I want it. What it does not do correctly is necessarly copy paste information for the correct hour of the day. For example if the computer goes down it will start again copy/pasting for the next hour readings. Even if its three hours latter. The log should be blank for three hours since those hours wwere missed. My code is below, how can it be fixed to run "Capture" macro for the correct time of each day and leave the rest blank? Rich
Public LastSoon
Function Soon() 'As Date
Dim tm As Variant
tm = Now
tm = tm + 1 / 24 'next hour
tm = tm - Int(tm) 'time only (no date)
tm = Int(tm * 24) 'on the hour
tm = tm / 24 - 5 / 1440 '5 minutes before hour
Soon = tm
View 9 Replies
ADVERTISEMENT
Mar 28, 2014
I am looking for a function to convert time given by my computer (Local time) in EST (Eastern Standard Time). We are several users of a same file (with timestamp macros) and all time need to be aligned to one time zone (EST), even if all users are working in different time zone (EST, CST and IST).
View 1 Replies
View Related
Sep 8, 2009
I need a sum function in A1 of a "Total"-sheet that totals cell A1 in every sheet with a certain color on the tab. The number of sheets can vary from time to time.
Any idea about a dynamic sum function that will do this, in combination with VBA?
View 2 Replies
View Related
Nov 17, 2005
I am using the count function for attendance tracking of Vacation, Personal Time, & Sick Time. (Example: = COUNTIF($F6:$CQ6, "V")
I need to be able to do half days. I have tried many different formulas/ways to incorporate the half day scenario even without using the count function with no success.
View 13 Replies
View Related
Nov 19, 2008
I 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().
View 6 Replies
View Related
Jan 25, 2009
D: is quantity to be made
G: is number of minutes this will take
J: is the time each order should be finished
Problem
at 12:00 work stops for lunch and resumes again at 12:30
Somehow I need this to be reflected J:
View 4 Replies
View Related
Dec 17, 2008
I m Trying to add some features to it and run into an issue. Here are the fields we are working with!
A1= 13:30:00 17/12/2008
(in other words 1:30pm on date given) - Need to know proper cell number format!!
B1= 23:00:00
(this represents 23 hours) - format for cell is [h]:mm:ss
C1= Unknown function
(This field is my problem!!)
Answer here SHOULD be "14:30:00 16/12/2008"
I need a function for C1 (using A1 and B1) that will give me the time and date 23 hours earlier or however many hours:mins:secs is specified in B1! Also need to know what number format to use for both A1 and C1
View 4 Replies
View Related
Jan 24, 2009
is it posible and if so how do I use the IF function to tell cell that
it has a time between 12:00 and 13:00 then add 30 minutes and if not then do nothing
View 4 Replies
View Related
Mar 26, 2008
I'm trying to calculate and average using a time frame in the day. I want to use a certain number "X" that correlates to a hour/minute in the current time. How do I write a statement to get me the correct "X" value?
example:
8:00 .005
8:01 .006
8:02 .0075
8:03 .0077
8:04 .008
time now is 8:03
How do I get the .0077 in excel?
View 10 Replies
View Related
Dec 9, 2008
I want to add two times together.
14:00 (time) + 03:42 (duration) = 17:42
Is there vba code to be able to do this or an excel function?
I have tried searching but strangely couldnt find anything?
View 2 Replies
View Related
Oct 6, 2009
I have a working week that starts at 07:00 on a Monday morning and finishes at 14:30 on a Friday afternoon.
For planning purposes I need to know at any given point how many working hours are left. The reason for this is so that I can multiply that figure by the amount of Engineers available which will give me the amount of Man Hrs left in the week.
For example - if it is 11:00am on the Tuesday, how many working hours are left ....
View 13 Replies
View Related
Dec 9, 2012
I am working on a spreadsheet that deals with times such as a work schedule, how every the schedule cells are in a time format, and my if function will only return "check"(which is false). my code works if it remove the time formatted cells but i want to keep the document in its orginal format.
=IF(Mngmt!F16="PH","",IF(Mngmt!F16="OFF ","",IF(Mngmt!F16="VAC ","",IF(Mngmt!F16="8:00:00 AM", "MIKE", "CHECK"))))
The bold and under line portion is where my problem is at.
Its looking to the mngmt tab to find f16 when it equal 8:00:00 AM (scheduled time in time format) but returns ""check"(false).
View 4 Replies
View Related
May 29, 2009
Function being called at inappropriate time
I have the following code in the active worksheet:
View 4 Replies
View Related
Mar 28, 2014
I am trying to calculate the difference in time between two cells and if the difference is >= 8 Hours the result is 8 hours - 1 Hour if the result is less than 8 hours then the result stands. I have the following, but it returns 8 always.
The two cells are formatted as hh:mm
H6 = 06:30 & G6 = 14:30
=IF(((H6-G6)>=8),"((H6-G6)-1)","(H6-G6))")
View 3 Replies
View Related
Mar 4, 2008
1) The following formula returns TRUE or FALSE:
=AND($J$15"",(MOD(SECOND(NOW())-1,6)+10) = COLUMN())
It works fine as long as the w/b is busy doing something!
2) Otherwise if the w/b is not busy, the formula appears to be calculated only ONCE at the current time NOW(), and F9 must be pressed repeatedly to re-calculate the formula despite Tools::Options::Calculation is set to Automatic.
3) Is it possible to have NOW() representing a progressive NOW()?
4) If not, can the above formula be modified such that it is evaluated continually for, say, the next 60 or 120 seconds from the current NOW()?
View 9 Replies
View Related
May 10, 2007
I have two worksheets which talk together and one of them gives a time stamp using some VBA code
=format(Now(),"dd/mm/yyyy AM/PM"
in the other spreadsheet I have the Now() function which has been working fine. However, when another user went into the spreadsheet went in it, my calculations between the two dates now error. I am presuming its because of the now function not having some formatting defined so is there anyway of changing this value? The cells-formatting will not obviously work as excel is getting confused between the two time types even though the format looks fine. To help the calculation that is messing up is
=IF($L7="","N",IF(DATEDIF($L7,$J$1,"d")>10,"N","Y"))
whereby L7 is the imported timestamp and J1 is now(). The calculation is to show which accounts become inactive after 10 days.
After reading up on the internet I know that to get round the problem I have to make sure that everyone is using the same regional settings. But I just have a couple of questions:-
1. I can understand the date calculation not working on the other persons machine, but when I went back to look at it I was getting the same error. Even though the file is shared, how come my settings didnt 'overwrite' and how come i was affected?
2. Is there a way to make the code work regardless of who opens it in whatever region? Im not sure if there is a fundamental problem but I cant rely on everyone having the same settings because I know that this will cause problems in the longterm.
3. Using the formuale Now(), is there no way of forcing that to a uk date format to stop the errors?
View 9 Replies
View Related
Jan 16, 2014
Using the attached doc, and its legend tab, I need the following columns corrected as I am seriously tripping up on formats vs inputs for outputs.
1.) Column "N's" result based on the duration output of the preceding column "M" is:
<=4 = 40
5 = 30
6 = 20
>=7 = 10
2.) Column "Q's" result based on the duration output of the preceding column "P" is:
<=18 == 40
>=19<=24 == 30
>=25<=48 == 20
>=49 == 10
The legend tab shows the info as well.
View 3 Replies
View Related
May 13, 2009
I have a function which i am using as part of a macro. The macro itself works fine and locates a search string i type into an input box across ALL worksheets in an excel doc. However, i have a function which takes the results and prints it to the front page, but when i try to use the worksheet name which has been passed to the function, i get the error: Run-time error '42': object require. this happens when i get to a line of code which says
View 2 Replies
View Related
Sep 21, 2011
I ran across a great article on performing Drive Time Calculations between two addresses. The only problem is that I can not seem to get it to work properly. Here is a link to the article cause I want full credit to be given to the person that created it.
[URL]
Here are the steps I have taken so far.
1. Downloaded Microsoft Internet Transfer Control reference (msinet.ocx) per [URL]
2. Registered the msinet.ocx
3. Selected Microsoft Internet Transfer Control as a Reference in Visual Basic.
4. Copied the custom function for DriveTime into a module.
5. Went to sheet that contained addresses (Column A contains PointA, Column B contains PointB)
6. In C2 I entered the formula =DriveTime(A2,B2)
7. After entering the formula I get a error (#VALUE)
View 9 Replies
View Related
Aug 23, 2008
I'm playing a game that requires me to keep track of money that is increased by a variable amount (pre-calculated and in a cell) and in a fixed time interval of 51 minutes weather I play it or not so I want to be able to keep track of the progress of the money gain outside of the game.
So, I need to know the macro coding for a real time counter that will increase the total money amount in one cell based on the variable income (declared in another cell) in the 51 minute intervals.
now... I also need excel to keep track of the value increase even if excel is closed (by the difference in time from when excel was closed last).
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 16, 2010
Ihave this exel sheet i nedd function to write the starting date and time immediatly after i enter the name in the last colume ineed from exel to write the date and time emmideatly if the case is (done, cancelled ,or rejecteted)if the case is (select status )i want the cell empty but if the case is (pending)iwant to the program to calculte the deffirence between the ClosingDatetimee(which written by the program)and the delivering date(which entered manually by user)
View 9 Replies
View Related
May 13, 2014
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...
View 3 Replies
View Related
Mar 27, 2009
The value that is returned is off by 2 rows everytime. When I evaluate the formula, it shows the correct row just before the indexing function does it's thing.
I have a cell phone bill for 20 or so phones and am trying to isolate one number at a time and evaluate usage. The first sheet is my data, the second is sheet ("Breakdown") is where I enter the number in A2 that I want to look at. When I do, it misses the first 2 rows and picks up 2 extra from the following phone number.
View 4 Replies
View Related
Dec 4, 2007
I have created a userdefined function. the Idea is for the user to calculate the difference in hours and minutes. The input would be like budtime = 30 , realtime = 1.25 the answer I would like to have is 28:45 (28hours and 45 mins). Im aware that I can format the cell manually with [h]:mm but I'd like the function to do it.
I have written this:
Function RT(BudTime, RealTime)
RT = Format(((CDec(BudTime) - CDec(RealTime) / 24)), "[h]:mm")
End Function
View 5 Replies
View Related
Dec 19, 2008
I have a worksheet which contains START TIME in column A, then TIME USAGE in column B and END TIME in column C. User enters start time, followed by the number of time usage in minutes, how could i possibly display the end time automatically in this scenario? how do you add the entered time usage to the start time to display the end time? Say if I enter 1:00 AM at start time and 00:15 minutes on time usage, how can 1:15 AM be displayed on the end time automatically?
View 2 Replies
View Related
Feb 7, 2014
the vendor has a 21 hr working window; start from 7am and goes until 4am; Mon to Fri.
Here is a scenario:
- i request for a product information from a vendor on 3-Feb-14 8:00am (Monday)
- he replies with all of the product info on 6-Feb-14 12:00pm (Thursday)
can you find the time in above scenario consideration the working window?
Here is another scenario:
- i request for a product information from a vendor on 6-Feb-14 8:00am (Thursday)
- he replies with all of the product info on 11-Feb-14 12:00pm (Tuesday)
- Sat & Sun are days off but keep in mind that my Friday shift ends on sat at 4am so the networdays formula wont work.
View 2 Replies
View Related
Mar 3, 2009
I am trying to provide a tool for department leaders to monitor productivity for order processing in their departments. The variables I have are: Number of orders(variable), number of pickers (variable), start time(variable). Then, I know each order takes 1 picker 4 minutes to pick on average, and there are 45 minutes worth of breaks during the picking process. So after entering the variables I used =(((C3*4)/60)/D3) to come up with the time needed to process the orders. What I can't get to is how to add this number to the start time, factor in break minutes and get to the projected completion time. I have Excel 2003 at work. Clearly I need to take a class!
View 4 Replies
View Related
Mar 22, 2012
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)
View 2 Replies
View Related
May 3, 2013
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.
View 9 Replies
View Related
