I'm trying to write a macro that compares 2 coloms(open,close) of dates/times and calculates how long each was open for. This is what I have so far. I've also include as an attachment an example of the date/time format i'm using.
Sub Time()
Dim A, B, C As Long
Sheets("Sheet1").Select
For i = 7 To 5000
A = Fix( Range("A" & i).Value)
B = Fix(Range("B" & i).Value)
C = A - B
Cells(C, i).Value = C
I'm working on the final stage of a project. I'm attempting to write code that will set off a chain of events if two dates are 91+ days apart from each other . I've attached a sample worksheet that shows the bare bones basics of what I'm attempting to do.
What will be the most efficient and effective way of accomplishing my goal: userform, functions, formatting, etc.? T
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 thousand rows of data in the following format:
Despatched Time Batch Date Time Amended Time
20/4/2013 3:45:00 1 20/4/2013 03:45
[Code] ..........
'Date' is generated by =IF(A2="", "", TRUNC(A2)) 'Time' is generated by =IF(A2="", "", IF(E2 = "", A2 - TRUNC(A2), TIME(LEFT(E2,LEN(E2)-2),RIGHT(E2,2),0)))
And sometimes I may want to change the Time, so I used another column 'Amended Time' to insert a 4-digit value to do so.
I want to arrange them into batches according to the hh:mm, so for the column 'Batch', I used the following formula: =IF(D2 = "", "", IF(EXACT(D2, D1),MAX(B$1:B1),MAX(B$1:B1)+1))
The problem is, when I changed the time by inserting 4 digits in 'Amended Time', the time value is actually different from those generated by TRUNC(), so even two cells have the same time '03:46', the time value is not exactly the same and so they are arranged into different batches (please refer to the following table).
I need to VBA code that will loop through the active sheet for all rows, column C2 through however many rows there are. Col C contains a date in the format: mm/dd/yyyy. I need the VBA to compare this date to today's date+30 days and if the date in C2, etc. is greater than today's date + 30 days, shade the row a particular background color (e.g., 3).
Assume there's an even number of rows containing textual data.
COL A text1 text2 text3 text4
I would like to compare the lengths of text1 to text2, and return the row containing the greater of the two/delete the row containing the shorter of the two. In either case, the contents below would then have to be shifted up by one. Then repeat for the next 2 rows all the way down to the last row using VBA.
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).
This workbook is designed to prompt the user with inputboxes allowing them to enter a name, hyperlink a file to that name, and then a date. Next all the drawing names are read into an array and stored. Along with some minor sheet formatting, this is what happens when the button "Add Drawing" is clicked.
Next, when the button "Revise Drawing" is clicked, the user will be prompted with an inputbox for a drawing name. This name will be compared to the names in the array. If the name is found, the row containing that drawing will selected and the user will be prompted to update the hyperlink, then the "drawing date" will be moved to the right by one column, then the user will be prompted for another date replacing the date that was moved. The first sequence of commands or button works fine. The button "Revise Drawing" prompts the user with the inputbox requesting the revised drawing name, then develops the runtime error 1004.
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.
take a look at the attached example and let me know how to make the formula work?
For each "title" in column A, I want to compare the two sets of start and end dates (columns B & C versus D & E) and if the date range in column B &C overlaps with the date range in column D & E, input "yes" or "no" in column F.
In my attached example, rows 8,9,11, and 12 would be populated with a "yes", the rest would be "no".
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.
I need to set a format in which if I enter a num eg 15 on the Jan Column, format comes as "15-Jan" format or If i type 20 on the march column , it comes as "20-Mar" format
I would need it in Date format, because I would be comparing dates later on.
What is the code i need to use to assign a macro to a command button which inserts the current date and time in the selected cell regardless of where that cell is?
I have a sheet with a list of dates in it that I wish to format to show the day as well as date and time. I am trying to do this automatically with a macro, below is the before and the desired after.
Before Format - dd/mm/yyyy hh:mm Appearance - 29/03/2009 00:30
After Format - ddd dd/mm/yyyy hh:mm Appearance - Sun 29/03/2009 00:30
This works fine when I format the cells manually, but when I try to format them via macro '29/03/2009 00:30' becomes 'Sun 29/03/2009 12:30' which is obviously a totally different time! Has anyone got ay idea why it might be doing this?
What I am looking for is a macro/formula that will look at a unit number, then the type of work done, then the completion date and decide if the same type of work was done on the same unit within a 7 day span of time.
Example
Unit is 11111, work is plumbing completion date is 10/4/07 Unit is 11111, work is plumbing completion date is 10/7/07
This means the work has been done 2 times (obviously showing that it did not get fixed the first time)
Is there any way to mark an X in the square with the formula, or any type of identifier what-so-ever
I have attached a spreadsheet and hope it helps
Again I know this is complex so it is OK if no one feels like messing with it but I wanted to check here first.
'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 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 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'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"
I 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 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.
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 ????
