Comparing Date/Time
Jun 19, 2006
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
Next
End Sub
View 9 Replies
ADVERTISEMENT
Mar 27, 2013
It is currently 11.40 AM. When I evaluate
Code:
?Now() > TimeSerial(11,30,0)
I get True.
However, when I evaluate
Code:
?Now() < TimeSerial(11,50,0)
I get False.
Why is this?
View 9 Replies
View Related
Apr 15, 2014
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
View 14 Replies
View Related
Jun 17, 2006
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?
View 3 Replies
View Related
Feb 28, 2014
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).
Despatched Time
Batch
Date
Time
Amended Time
20/4/2013 3:45:00
1
20/4/2013
03:45
[Code] ..........
View 9 Replies
View Related
May 12, 2009
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).
View 9 Replies
View Related
Jun 12, 2009
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.
View 3 Replies
View Related
Jan 2, 2013
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).
View 4 Replies
View Related
Jul 15, 2009
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.
View 3 Replies
View Related
Oct 24, 2007
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.
View 12 Replies
View Related
Feb 21, 2013
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?
View 3 Replies
View Related
Apr 22, 2008
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 Related
Dec 4, 2012
[URL]
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".
View 3 Replies
View Related
Jul 19, 2014
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.
View 7 Replies
View Related
Jul 8, 2013
I have a problem regarding month formatting
I have 12 columns, Jan 2013 - Dec 2013.
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.
View 2 Replies
View Related
Oct 20, 2009
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?
View 5 Replies
View Related
Oct 13, 2009
I'm trying to write a VBA code for converting the date/time as text to a date/time value.
The scenario is, I have three different types of text :
ddmmmyy
HH:MM
ddmmmyy:HHMM
I could have the code working fine with the first two types, but not the last one.
View 4 Replies
View Related
May 21, 2008
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?
View 6 Replies
View Related
May 31, 2007
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.
View 3 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
Mar 7, 2014
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.
View 9 Replies
View Related
Jun 17, 2014
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
View 1 Replies
View Related
Sep 9, 2012
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 Related
Sep 6, 2006
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
View 6 Replies
View Related
Nov 25, 2006
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.
View 7 Replies
View Related
Oct 12, 2009
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
May 30, 2012
I want to add hours to a date-time cell to get result in date-time.
Format of cell A1 is d/m/yy h:mm AM/PM
Format of cell A2 is General
Format of cell A3 is d/m/yy h:mm AM/PM
I want to add A2 (number of hours) to A1 to give A3.
The formula I used is A3=A1+Time(A2,0,0)
The formula works perfectly fine when A2 is less than 24, but when A2 is more than 24, the date doesn't get changed.
View 6 Replies
View Related
Sep 23, 2009
I have a file that has the Date and Time combined into one cell. I want to separate the two, and cannot find anywhere on the net to do so!
This is the cells format:
d/mm/yyyy h:mm
Cells look like this:
28/05/2008 12:30
View 6 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
