I have a list of dates with an hour inside them . I am trying to remove teh hour from the field , and leave only the date. teh format option just hide teh hour but not removing it how can it be done ?
View 4 RepliesI have obtained the following data which I need to put into a spreadsheet to import into an accounts program. I can't change the way I get the data which is as follow
Hrs worked Rate
05:55:00£30.00/Hour
07:40:00£21.00/Hour
05:45:00£30.00/Hour
What I need to have is 5.92 30.00
7.67 21.00
5.75 30.00 ie the time format in decimal and loose all the unnecessary symbols etc for the rate
What i need is to be able to concatenate a field such that it includes current system year,month,day and time. My attached spreadsheet illustrates my current formatting to achieve year,month,day. But I seem to be challenged in getting the current time to display properly.
View 4 Replies View RelatedI want to display a date and time in local time, is there a simple formula to do this?
View 3 Replies View RelatedI have data exported from a defect management tool (that reports all the defects that have been recorded in the defect management system). This data has unique columns for name of originator, defect ID, date originated, priority, etc. The date originated column has the date and time. I want to create a report that tells me how many High, Medium and Low priority defects were created on each date - and I am using a pivot table to do this.
However, because the date column is actually the date and time the defect was reported/recorded, the time element of this is making all the dates unique eg, I have three defects recorded on 29/01/2009, a High priority defect (reported at 29/01/2009 12:23) and two Medium priority defects (reported at 29/01/2009 13:02 and 29/01/2009 13:32). My pivot table is showing each date as a unique date - with a count of 1 against each, whereas I want to see 1 date entry (for 29/01/2009) with a count of 1 against High and 2 against Medium.
I have tried custom formatting the date column to dd/mm/yyyy - which appears to work in the data (but doesn't in the pivot table, because the time stamp is still there, just not visible). I have tried copy and paste special [Values] (on the custom formatted date column) -but the time stamp is still there. I have tried formatting the date column to "General", but this changes the dates to the number format - eg 29/01/2009 12:23 becomes 39842.5166203704.
I have tried inserting a new column and using the =Left function for the first 10 characters of the cell (ie the 29/01/2009), but that gives the first 10 characters of the number form of the date, ie 39842.5166. Basically, without going into every date cell (and I have thousands) and manually deleting all the times, how do I quickly change the date column into just having the date in it (ie how do I strip off the time element)?
figuring out a formula to count the number of occurrences within a date range (the month of October) however, the date column is formatted to mm/d/yy 00:11:22 PM/AM.
I tried using =COUNTIFS(B4:B96,G3:G9611/1/2008) but got an error.
This is what I'm working with (there are other occurrences for Nov and Dec in the spreadsheet - this image only shows Oct)
Site Statistics
October-December 2008
Visitor
No.
IP
Address
Operating
System
Browser
Site
[code]....
I have 2 fields formatted like this:
1) Date: "30/10/2013"
2) Time: "10:56:39:000"
I need it to read/merge into: "30/10/2013 10:56:39:000" and then be able to be able to be converted intp seconds as a UTC Unix epoch example formula =(A1-25569)*86400.
i have to removed the duplciate records with older dates. The date column is made up of date and time.
IDExpiry Date
1202 Mar 2013 12:57:04
1220 Apr 2013 10:49:24
5020 Apr 2013 10:57:06
5021 Oct 2013 07:20:54
5024 Oct 2013 08:41:56
I 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 a large data set which contains four coloumns: Supplier, Supplier number, order number, and date/time of delivery. The date/time coloumn is formatted as YYYY-MM-DD HH:MM with a 24h time notation. What i want to do is to find deliveries that occurs within 1 hour and that are from the same supplier. So i basically want to group (?) the data with regards to the suppliers and then, within these subsets, check for date/time entries that occurs within 1 hour from each others by "reading" each date entry and compare it to the following one(s) (and maybe stop comparing when the 1 hour interval is passed)?
Furthermore, even if this one might be very hard, it would be good if i could make sure that the entries that are "tagged" as within a 1 hour interval, wont be used as basis for a new interval or be included in other intervals.
The result i am after would be number of 1 hour intervals for each supplier and the number of entries in each interval.
Below is an example from the date/time coloumn:
12-03-08 15:32
12-03-08 15:33 ... Interval with 2 entries
12-03-12 14:54
12-03-28 11:57
12-04-16 09:10
12-05-07 13:41
12-05-07 13:46 ... Interval with 2 entries
12-05-28 11:55
12-05-28 12:00
12-06-04 12:01 ... Interval with 2 entries
12-06-04 12:09
12-06-11 08:30
12-06-11 08:31
12-06-11 08:59 ... Interval with 3 entries
12-07-02 11:10
In the following extract, I would like to have today's date placed into Col K when an entry is made into Col J.
Entry into Col J is from a validation list of 3 options. What I desire is that when one of these options is selected ("Complete"), then I would like today's date to be written into Col K.
Currently Col K has a validation list which comprises dates from 1st of each month from July to June.
I need a fixed date as text preferably, so was thinking the VBA function "Date" on a Change Workbook Sub.
The information is currently part of a Table in Excel 2007 that has about 500 records.
IdeasList
*
J
K
2
Status
Month Completed
3
In Progress
*
4
Complete
Jul-13
5
*
*
6
*
*
Data Validation in Spreadsheet
Cell
Allow
Datas
Input 1
Input 2
J4
List
*
=ValidationList_Status
*
K4
List
*
=ValidationList_Months
*
in coverting these fields into a date field.
Example 91306 to 060913
I have encloed the file.
I am trying to count the number of times that data is in a field and in any field the date is greaert than today's date. This date field could be blank and that is where I am having the problem.
Example
See Attached
I have a userform that has a time field on it and then the userform displays the time as a number.
Ex. I entered 11:00 AM in the Time of Contact field and the form displays it as 0.458333333333333.
Is there a way to modify Pivot Table so that "Sum of, Count of", etc is not shown. For example, have Table (& Pivot Chart) show "West Region" instead of "Sum of West Region"
View 13 Replies View RelatedI have a list of addresses, which are comma separated, all ending with a PostCode
How Can i strip out the PostCode field?
VBA code to remove "sum of" from a pivot table. It works on a regular pivot table. However, when applying this code to a pivot table created utilizing PowerPivot, the code does not work. Here is the code:
Sub ChangePTName()
Dim pt As PivotTable, pf As PivotField, ws As Worksheet, i As Long
Application.ScreenUpdating = False
Set ws = ActiveSheet
For i = 1 To ws.PivotTables.Count
Set pt = ws.PivotTables(i)
pt.ManualUpdate = True
For Each pf In pt.DataFields
[Code]...
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 need to sum the Time field, if the Temp Zone is = F. My problem is, as shown on the sheet, there are multiple days. As well, I need to pull the information for the totals (looking at 6 days worth of info) into another worksheet where the names (first column) are not in the same order, as some of the names are on this sheet, and some are not. I had a massively large vlookup statement, but of course, it's rather large and doesn't copy over well (when changing the days, it looses the last couple of lookups) ......
View 20 Replies View RelatedI have some intraday date that looks like this, hope the formatting comes thru when i post this....anyhow, the second column is TIME (ie 15:25 15:30, etc...5 minute time intervals for SP500 stock data. I want to have a column that simply subtracts row 2 column b (time column) from row 1 column b.....when i do this i get a #value....I know this must be possible to get a result i need....ie 15:30 - 15:25 = 5
Do i need to reformat the TIME column into something different? ....
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 data that I received from a feed. Each cell displays a value like 100, 200 or 300 or a time like 10:41:52 AM, but the formula bar depicts the array information ie =Q|TS!'AVAVA,45000,DEIPSCT'.
I'm working with the data where basically the time is the independent variable and the number is the dependent, very basic stuff BUT I cannot figure out how to reference the time.
Just trying to write a very simple if function such as =IF(G17="8:50:17 AM","start") and I can't figure out how to do it. Cell G17 is showing 8:50:17 AM.
I need VBA code to sort the birthdays for all the employees in my company.
See the attached file for example.
I need to sort according to their month and then date and not with their year.
i.e it should not consider Year for sorting.
How can I have a form field automatically fill some aspects of the data that's input? I've seen it before, like a phone# field automatically including the dashes.
I would like to automatically include colons in a time field. So if I were to type "081500" (or even "81500"), it would complete it as "08:15:00".
I am trying to enter a blank row everytime the 2ND character of a field changes...
sample cells (ALWAYS COLUMN B)(last row needs to be calculated as it changes daily):
2T2W3D3L4H4N4N4N6C6C
when done:
2T2W3D3L4H4N4N4N6C6C
was trying this:
Code:
Dim chkConfirmRw, LastNameRow As Integer
For chkConfirmRw = LastNameRow To 1 Step -1
'Compare the current cell to the one below it
'If they don't match, insert a row row below the current Row
If Range("B" & chkConfirmRw) Range("B" & chkConfirmRw + 1) Then
Range("B" & chkConfirmRw + 1).EntireRow.Insert Shift:=xlDown
End If
'Decrement the counter and do it again
Next
but it does not work.
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.
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.
In the "Pivot Table Field List" I have the option to "Choose fields to add to report". I have a large number of fields that I want to choose and "Add to Values" - so it is tedious to select each item individually.My question: Is there any possible way select multiple fields at the same time? e.g.
a "select all" option, orhighlighting a range of the available fields (either by dragging or holding the shift key)I've looked / tried above with no luck.
Any Excel Add-Ins out there that might address this? I'm fearing not since my searches have come up empty.