I've attached a small sample of an excel file I created from a CSV. The date has defaulted to American format and the time is, as you can see, messy. How can I reformat so that I can have British date format and 24 hour clock?
View 2 RepliesHow should i reformat the date and time picker? at this moments, it shows the date in the format of mm/dd/yyyy but the format that i want is dd/mm/yyyy.
View 10 Replies View RelatedI 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
I m trying to use a variable that is declared as a range in a formula. GroupCategory is the name of the sheet its getting the data from. I want to use myRange instead of actually putting in the range. I know there's something wrong where I highlighted in red.
Sub AAmacro()
Dim myRange As range
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Set myRange = Range(Cells(2, 1), Cells(Lastrow - 1, 1))
Sheets("sheet1").Select
Range("L2").Formula = "= SUM(INDEX(GroupCategory '!' & myRange,,MATCH(C1,GroupCategory!$A$1:$ AI$1,0)))"
End Sub
I am rewriting functions for my companies inventory spreadsheets. Excel for some reason took the item codes and formatted them into date format. Right now it is in 10/01/1902 format, and it needs to be in 10-1902 format. I've tried reformatting the cells but so far I haven't found one that works.
View 5 Replies View RelatedI have VBA code to perform some actions on data in excel file and then convert all that data into semi-colon separated CSV/text file (code below).
Now, all I want is to add VBA code in the existing macro to find a column header (say, "Application date") and then convert all the dates into YYYY-MM-DD format. The original values in this column don't have a fixed date format.
Code:
Public Sub ExportToCsvFile(FName As String, _
Sep As String, SelectionOnly As Boolean, _
AppendDataOnExistingFile As Boolean)
Dim WholeLine As String Dim FNum As Integer
Dim RowNdx As Long Dim ColNdx As Integer
[code]....
I am given two seperate times and need to calculate the variance, which I have. However the variance comes out as a number and I need to present this in time format.
The format of the original data (in column A and B) is dd/mm/yyy h:mm:ss
I know I can *60*60 my variance answer (column G) to get seconds (column H) and then divide this answer by 60 to get this to mins and seconds (column I) but this just looks too messy.
Need a point in the right direction with this one! I want to calculate the difference between two time values, the problem is there two different formats as below:-
1) 520.00 - this is a number format
2) 500:00:00 - this is a time format HHH MM SS
What could I use to show I have a 20.00 hour difference???
Extract data with differing date formats that I need to convert to something consistent that I can format as a date.
This is an example of data.... all with general format at the moment.
2/28/2013 2:48:53 PM
1/16/2013 10:48:50 AM
12/17/2012 11:59:49 AM
I have used this formula to extract the date portion, but I can't get this to then format as date. How do I convert this to the julian date, so I can then apply a date format?
=LEFT(G9,SEARCH("/",G9)+7)
(The day portion of this date always has a leading zero).
how excel deals with numbers/times?
Specifically, I have some times similar to below
8:56.47 (8 mins 56 seconds 47 milliseconds)
4:32.12 (4 mins 32 seconds 12 milliseconds)
I want to compare times, work out differences, and graph progress.
Yet is seems that excel is storing them in date formats, despite me specifically setting it as mm:ss.0 custom format.
I've been trying to create a work schedule and I am having trouble formatting it just the way that I want it. Basically, I am looking to put in the time with an AM or PM after it.
The problem that I am having is that if the time is on the hour I want it to display as 4 PM and if it is not on the hour, then I want it to display as 4:30 PM. I don't want 4:00 PM with the 2 extra zeros. Is there any way to do this with conditional formatting or some type of formula? I just don't want to have to change the number format every time I have to enter a time that is not on the hour.
I am attempting to calculate % utilization. I have number of minutes used formatted in [mm]:ss and number of minutes available formatted as a number. When I divide available by minutes used I get a percentage that is off.
So I have Available 33600 minutes available 4901:20 minutes used.
I have a worksheet where in a column, dates are stored in various date format i.e. it may be DMY or MDY or YMD.
08-06-12
08-06-12
08-07-12
08-07-12
13/08/2012
13/08/2012
13/08/2012
14/08/2012
Above is just for an example, above dates are for the month of August, but as can been some cells are in MDY formate and some in DMY. Can I have formula to convert different date formats into one date format?
I'm running excel 2000 and don't have access to mscal.ocx, so i have used the date time picker with a calendar, but the formatting of the calendar is in US format m/d/yy and I need it in UK format d/m/yy, I have narrowed it down the following piece of code, the D values are the days,
View 12 Replies View RelatedI need for my office a table which has one column with dates. The "issue" is, that sometimes I know the whole date (day, month, year), sometimes only the month and year and occasionally just the year. Is there a way through custom number formatting that excel behaves correctly? How would I need to set conditions to achieve this? Because now something happens which is 99% incorrect .
View 6 Replies View RelatedI have data sent to me with different date formats on the same spreadsheet; I used Format - Cell and format date to this format: yyyy.mm.dd. However, only some of the data changed to this format and rest remain the same. I've tried many other ways, but didn't work.
View 14 Replies View RelatedI have a userform which allows data to be entered onto a worksheet. One of the fields (a textbox with the standard calendar control) is the date. When the transaction saves, it correctly saves the date as dd/mm/yyyy using
ActiveCell.Value = TxtDate.Text
I also have a form which allows the user to amend a transaction by loading the data onto the form, amend it and save it back to the worksheet.
The problem is that when it picks up the data and re-saves it, it's resaved in the format mm/dd/yyyy which is a problem because I use the month as one of the categories that users can filter the data by.
I also have the following line immediately after each time the data is saved from the form to the worksheet:
Selection.NumberFormat = "dd/mm/yyyy;@"
I have a simple piece of code that gets a date from the user via inputbox in the format dd/mm/yyyy. That date is then put into a cell (e.g. B1), and the cells to its right get given something like "=B1+2", "=C1+5" etc so that they will all show a date a few days further down the track. (Much more complicated than that but this is the basic idea)
The entire row is then formatted "ddd dd mmm yyyy" with the intention of every column getting a header like "Wed 27 Jan 2010". This works perfectly for every column EXCEPT B which shows the the string "27/01/2010" it was given from the inputbox.
Right clicking --> Format Cells shows every cell in the row has the exact same 'Custom' format, and the 'Sample' at the top looks correct for every cell except B1.
BEFORE the macro runs and formats all the cells, but AFTER they have been given their formulas, they all display something along the lines of '40021', '40023' which I assume is the number of days since Jan 01 1900 or the like - proving that THEY knew the value in B1 was a date, so why doesnt B1 itself know?
I have a userfiorm with a text box that auto fills as follows
PHP Code:
Fill Datebox DateBox.Value = Format(Date, "dd/mm/yyyy")
which today fills the text box as 01/03/2012 (UK format), however when i try log a record as follows
PHP Code:
copy the data to the databasews.Cells(iRow, 1).Value = Me.DateBox.Value
it changes the date to the american format so that it is pulling through on the data base as today being the 3rd Jan 2012.
I am importing data (using filestream) from txt files with comma delimited values. When I import dates into General type cells it turns a date of dd/mm/yyyy into mm/dd/yyyy only if the date makes sense.
I would like it to import dd/mm/yyyy as dd/mm/yyyy.
I am also interested to know how Excel/VBA deals with dates coming from various sources, what quirks or mode of thought it follows.
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 amend that column to display 26 Mar ( or 12 Dec or 17 Apr etc depending on value).
Try as I might, using format cells, nothing results in the required display. I'm not even sure what format the cells were in as received, since highlighting the received column and selecting "format cells" does not reveal the initial formatting. If I try pasting special to a new column and selecting values, I just get the numerical date value as a 5digit number.
I have a spreadsheet that has two columns. Column A is a date format and column B has the number of days between both dates over 2 years analysis is =A8-A7.
However when i come to build a chart its all wrong as the dates are following a weekely format, can i adjust this to pick up only the dates that are showing on the cells in column A? also is ther ea way which teh #VALUE! doe snot show?
02/07/2012
14/08/2012
43
00/00/0000
#VALUE!
[Code]...........
I need to change the date format in a cell which also contains text.
The full formula is as follows:-
="Report period:
"&TEXT(Control!B9,"dd mmmm yyyy")&" - "&TEXT(Control!B10,"dd mmmm yyyy")
This gives me the following:-
Report period:
01 October 2012 - 30 September 2013
What I would like to have is the following:-
Report period:
1st October 2012 - 30th september 2013
It's only a minor change but I think it looks better. Without a hideously complicated formula, if this is possible using one of the standard cell formats?
An in-house server spits out a whole lot of dates which I put into Excel and then use the dates for analysis (VBA code). The problem is that Excel is getting confused about the date formats. Sometimes the dates are formatted as dd/mm/yyyy and then the next line can randomly be mm/dd/yyyy. This totally messes up all changes of having data integrity.
View 5 Replies View RelatedI have data which is exported from my system which includes the job date and gross profit amount. I want to find the gross profit total for each day based on a matching date. However, the data exported includes the time in the date cell and Google Sheets won't match it. I'd like to avoid using a helper cell if at all possible.
You can see the formula here : [URL] ....
Formula is on the Q column.
I'm new to VBA so my i'm having alot of problems figuring out simple stuff.
Below is my script and when i run it, the dates turns out incorrect.
I noticed this only happens to dates that are before 13th of each month.
Example,
1st May 2008 ( 01/05/2008)
will turn up as
5th Jan 2008 ( 05/01/2008)
However when i manually open the file Todays trades.csv
The date looks just fine.
Is there some problem with using VBA to call up the file?
I am trying to find dates & time within text in a cell & return to a separate cell. The issue I have is that the date format varies frequently. I also can't rely on searching for "Sent:" as this also varies frequently
e.g. From: ########
Sent: 17 November 2011 11:57
I would like to return 17/11/2011 11:57
From: #######
Sent: 01/11/2011 11:50:13
I would like to return 01/11/2011 11:50
From: ########
Date: 05/11/2011 09:45:13
How can i search for various forms of dates and return them into a cell?
I have inherited a spreadsheet that I need to update soon. It takes data that has a date field and creates a pivot table by month. The person who created it completed it thru February. Now I have added data for March and April, but the pivot tables can not find this new data and I suspect it is because of the date formatting (I have done the Change Data Source under PivotTable Options to include all the new data).
The reason I suspect the date format is that she built a button to run a macro named "dateformat", which is not in the workbook. I have tired to manually format the date using the format painter from the February dates that the pibvot table recognizes, but I still get no results.
I have two columns containing dates (Date1 and Date2). Date1 is like a long date and Date 2 is a short date. I need a macro to compare these two dates and delete rows where Date1 <> Date2. Please find attached the before and after file which also contains the date formats for these two dates.
View 8 Replies View Related