I have two columns... 1 with a list of dates (ex. 22-Apr-07) and the other which states either a pass or fail. Ignoring the day and year, I'm trying to get the total # of references for each month, and only count those with a 'pass'ing grade.
I have just added two date and time picker controls to my spread sheet to allow the user to select the date range.
They are linked to cells C9 & C10.
The problem is that the formula I using to check whether the first selection pre-dates the second has stopped calculating correctly when using the date and time pricker controls.
The formula is:
=IF(C9>C10,"Error","OK")
I have checked it on the same sheet by just typing the dates in and it works fine...I have tried formatting cells C9 & C10 to short date, general, text, number but it still does not calculate correctly.
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 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 RelatedI 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 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.
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 RelatedI'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.
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?
I have a numeric value in G2, (in this case 7).
I have a date value in H2 - in I2 I want the date which is the date in G2 plus the number of days in H2, (so 20/01/2014 + 7 would give 27/01/2014 for example).
BUT - I also want it to account for a scenario where the value in G2 is 'No Expiry', so in that case I want I2 to show 'No End Date'.
I want me to track our inbox. All jobs that go into the inbox get logged on a sheet and at the end of the day get transferred to an Excel spreadsheet. They now want to track how long s job sits in the inbox before it's logged out and worked on. Currrently we put a time and date for both logged in and logged out. I've attached below a sample spreadsheet and a brief description as to some restrictions.
Our hours of work is 7AM to 6PM. So we only want to add add up the time the job was in the inbox based on those hours.... ie if a job came in right at 6PM at night and was logged out at 7:05AM in the morning, the total time that job was in the inbox was 5 mins. Same thing applies for the weekends.
I'm suppose to be in charge of creating that part of the spreadsheet.
************************************************************************>Microsoft Excel - work time.xls___Running: xl2000 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB3=
ABCDEFGH1********2*Job*#Time*inDate*inTime*outDate*outTime*spent*in*inbox*3*111:15*AM09/11/062:30*PM09/11/063*hours*&*15*minutes*4*24:25*PM09/11/069:45*AM09/12/064*hours*&*30*minutes*-*overnight*5*34:45*PM09/08/069:35*AM09/11/063*hours*&*50*minutes*-*over*weekend*6********7*hours*of*operation*are*7am*to*6pm***8********9*Explaination*or*#3*from*above….******10*Job*came*in*@*4:45*PM*on*Friday*but*was*not*removed*from*th*inbox*until*9:35*AM*on*Monday*morning.*11*Since*our*hours*of*operation*are*from*7AM*to*6PM,*the*time*that*the*job*was*sitting*in*the*inbox*was*3*hrs*&*50*mins.*12*This*is*figured*out*by*4:45PM*to*6PM*is*1*hr*&*15*mins*and*7AM*to*9:35AM*is*2*hrs*&*35*mins.**1hr,*15*mins*plus*2hrs,*35 *mins*equals*3hrs*&*50*mins*13********Sheet1*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I am trying to create a timeline spreadsheet for a weekly radio show I produce. We have 3 segments and the total time of the 3 must add to 59 minutes. Within each segments there are numerous variables with a certain time value. I am trying to figure out how to have time reduced correctly. For Example
Segment 1
Story A 5:00 minutes
Story B 4:30 minutes
Story C 3:00 minutes
What I need is Segment Total (A+B+C) in one cell and Remaining time (from total 59 minutes) in another. I know this may seem silly for most of you, but I cannot get the cells to format properly at all.
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 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"
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
Im having an awful time with the time it takes to calculate my formulas. I have probably written some of them ineffiecent.
=INDEX( Website_Query!$A$2:$Z$10000,MATCH(1,(Website_Query!$A$2:$A$9725=B1144)*(Website_Query!$C$2:$C$9725="EA "),0),4)
=Q1144*2
=IF(ISERROR(VLOOKUP(A1144,pricing!A$2:AL$1547,38,FALSE)),"",VLOOKUP(A1144,pricing!A$2:AL$1547,38,FALSE))
=IF(ISERROR(Q1144+AR1144),"",Q1144+AR1144)
=IF(AK1086="q2",AL1086+AS1086,IF(AM1086="q2",AN1086+AS1086,IF(AO1086="q2",AP1086+AS1086,IF(AQ1086="q2",AR1086+AS1086,""))))
="q"&INDEX(Website_Query!$A$2:$L$7725,MATCH(1,(Website_Query!$A$2:$A$7725=$B1086)*(Website_Query!$C$2:$C$7725=$C1086),0),5)
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 RelatedI 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.
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
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 ????
I have a large dataset where the first column is date and time i.e. "20/01/2005 03:41:06" and I want to delete certain rows based on the times. I have already tried playing about with macro's but failed fairly spectacularly so far. I have code from someone else to delete cells if the value equals a certain time but this doesn't work as the cell contains the date too.
I have already recorded one macro to reformat the data to as the software return 10 timestamped samples per hour and I want 8 i.e. every three hours so there is constant separation for statistical purposes. Both the macros are shown below so you can see what I have.
Ideally I would like to replace the line - If (r.Cells(n, 1) = TimeValue("22:41:06")) ............ - with one that reads - If (r.Cells(n, 1) CONTAINS TimeValue........ - but I don't know if that is possible? If not is there a way to separate the time from the date into 2 columns and then I can delete rows based on the time column using the code below? .......
I have a Excel Dates & Times column where the time is not always used. In these cases the time is 12:00AM. Is there a way to Custom Formats the cell so that the time is only visible if it is not 12:00AM?
View 6 Replies View RelatedI need a formula to add just the time to ' date and time', ignoring weekends.
eg:
Fri 24-Aug-07 10:52 is the date and time
28:48:00 is the time
If I add the time to 'date and time', result is coming as Sat 25-Aug-07 15:40
But it should come as Mon 27-Aug-07 15:40 (hence ignoring weekend)
I have a problem regarding sorting data having date and also time within a single cell.
Example data (I have written it as code to preserve formatting)
A B C D E F
12/5/2008 02:072/5/2008 06:0128804833363
22/5/2008 18:012/5/2008 18:0599271297
Column B is start date and time whereas Column C is end date and time.
My aim is to cut and paste the whole row automatically to Sheet2 if the time is within 2AM to 8AM else leave as it is.Also I don't know anything about VBA Script.