Custom Time Formats
Sep 8, 2008
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.
View 9 Replies
ADVERTISEMENT
Mar 20, 2009
I need to create a custom number format for a recorded delivery number in an excel sheet. I want it to look like AA 1234 1234 1GB in the cell even if the user enters it without spaces e.g. AA123412341GB. I have had a bit of a play around with underscores to get spaces but nothing seems to make it change.
View 4 Replies
View Related
Jan 27, 2007
I would like to display the following number of hours as day hour minute format
48.0 hrs as 2d 00h 00m.
View 4 Replies
View Related
Feb 20, 2007
i've inherited a spreadsheet that is causing me a problem with a custom format. I have two cells each with different custom formats. The first (cell A) is formatted as thus 0.0;0;. The second ( cell B) formatted as 0.000;0;
The number in each cell will vary. Cell A is always positive and cell B could be positive or negative. The problem occurs in Cell B when this should be a negative number as it picks this value up from another cell ,say cell C. so Cell C= -0.614 but cell B shows this as being 1. I'm sure the author of the sheet did this for a reason. Why does the cell display the number 1
View 3 Replies
View Related
May 17, 2006
I need to create some custom number formats, but after a few failed tests I
have a lot of junk that I'd like to remove. I've checked the Microsoft
knowledge base which suggest there should be a delete button, but I don't
have one.
View 14 Replies
View Related
Nov 11, 2011
I want to display a cell with the value -1,000 as Negative 1,000. In custom number formats I have selected #;"negative" #,###;; which does indeed display the cell with -1000 as Negative 1,000, however, I want to be able to link to this cell and have the link pull in Negative $1,000.
View 9 Replies
View Related
Feb 28, 2013
When you right click on your mouse, and you select:
Format Cells...NumberCustom
You can see many formatting styles.
Some of them impossible to delete eg: "General" or "@"
But some others easy to delete one by one pressing the Delete button.
In my Workbook eg "#.##0,00 ;[Red]-#.##0,00 ;; @"
Is there any way of deletting all of then by vba.
I mean all the different custom formats that I am carrying from book to book, from job to job long time ago.
I would like to have just the ones excel gives us by default, not the rest.
View 1 Replies
View Related
Apr 2, 2009
I have four different cell.
Cell a1 contains 6 digits
Cell a2 contains 1 digit
Cell a3 contains 6 digits
Cell a4 contains 4 digits.
Cells a3 and a4 may contain a number less than number of digits specified above but are custom formatted 000000 and 0000 respectively to ensure they have the correct number of digits. My problem is when I try to combine the four cells they lose their formatting. I have tried =A1&A2&A3&A4 and = concatenate(A1,A2,A3,A4). But both formulas loose the formatting from the individual cells.
View 7 Replies
View Related
Mar 19, 2014
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.
View 4 Replies
View Related
Jan 30, 2007
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???
View 2 Replies
View Related
Mar 14, 2007
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.
View 9 Replies
View Related
Dec 1, 2009
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 Replies
View Related
Mar 13, 2013
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.
View 2 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
Sep 21, 2011
I ran across a great article on performing Drive Time Calculations between two addresses. The only problem is that I can not seem to get it to work properly. Here is a link to the article cause I want full credit to be given to the person that created it.
[URL]
Here are the steps I have taken so far.
1. Downloaded Microsoft Internet Transfer Control reference (msinet.ocx) per [URL]
2. Registered the msinet.ocx
3. Selected Microsoft Internet Transfer Control as a Reference in Visual Basic.
4. Copied the custom function for DriveTime into a module.
5. Went to sheet that contained addresses (Column A contains PointA, Column B contains PointB)
6. In C2 I entered the formula =DriveTime(A2,B2)
7. After entering the formula I get a error (#VALUE)
View 9 Replies
View Related
Dec 15, 2008
I have a lot of data in the following format: 20080531T085930. Formatting the cells with yyyymmddThhmmss doesn't seem to be working. What's the correct way to handle this?
View 3 Replies
View Related
May 13, 2005
I'm trying to use the Date Time Picker control as a time picker and only want hours and minutes to show. Can this be done with its custom format property (Example)? The usual number formatting string doesn't seem to work.
View 6 Replies
View Related
Jun 10, 2013
I'm a fan of the Excel 2010 table styles, but can't figure one thing out. When I create a table in excel (Ctrl+T), I like to reformat it with a new defined style. When I save the style, I can use it while I'm in that instance of excel, but when I close and reopen excel, the style is gone. How to save the custom formats to they are always available?
View 3 Replies
View Related
Feb 15, 2014
I'm trying to make a converter between about 8 various types of values. These are not units like Km or miles or something like that, but rather numbers that represent a specific "hardness value" on a variety of scales (to name a few: HRC, HRA, K)
What I've been doing so far is plotting the two types against eachother and then getting the best trendline I can so that I can use that formula to convert between the two with relative certainty. (for example, when plotting HV vs HRC my fourth order polynomial trendline with an Rsquared of 1 is y=0.0001x4 - 0.0188x3 + 1.0768x2 - 20.709x + 350.69)
My questions comes up where I was hoping to make a window or box of some sort allowing the user to input a numeric value, then selecting the Input units and the hopeful output calculated units, and have the box spit back to the user the conversion.
View 9 Replies
View Related
Sep 2, 2008
I want to be able to create a range of VBA userforms to quickly perform long tedious tasks. I want these userforms to be accessed from a nice tidy toolbar.
I have done this and it looks nice and works well. What I would like to be able to do is have my custom toolbar of userform controlled functions be transferable so that if someone else wants my toolbar and attached functions they can install it easily much the same way you can do with an add in.
Is this sort of thing possible or does it require them to manually install all my userforms, modules and toolbar? If it is possible what sort of things should I be looking at?
View 9 Replies
View Related
Aug 13, 2005
This error occcurs when i add some worksheets to a workbook from another
one. I am not completely sure (cos this is not my work actually) but it
seems to me that there is not really too much (about 4000?) "different" cell
formats in the workbook, but there is a quite lot amount of drawing objects
(grouped technical drwaings plus autocad objects which i also converted them
to bitmaps to overcome the error).
I also dont understand the restriction:
If i have 3999 cells formatted "bold" and another 2 formatted "underlined"
this should not count 4001. True?
My workbook has about 15 worksheets with each fits to 2 printing pages.
View 13 Replies
View Related
Feb 12, 2009
I have a spreadsheet with a number of columns. In column D there are the following options: 2,3,4,C,T. I want to color the cell if there is data in the cell and column D contains a value.
So for cell L17, my conditional format I have =AND(D17=2,L17<>"") - - (RED), =AND(D17=3,L17<>"") - - (YELLOW), =AND(D17=4,L17<>"") - - (GREEN).
I need to color for values of C and T: but there are only 3 conditional formats permitted. Is there any way around this?
View 14 Replies
View Related
Oct 21, 2009
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 Related
Jul 20, 2008
I have a sheet that shows sun, mon, tue........,sat - all 7 days.
Under each day there are 3 cols so 21 in total.
Data is being entered into cells each day but as the week goes on it gets more difficult to match up the col & row, to many numbers.
What I would like is a way to highlight the whole range of cells per day in a different colour but only when data has been entered under a day, so if no data then no colour fill.
I only need 2 colours, 1 for sun, tue, thurs, sat &
1 for the remaining days.
=COUNTA($A1:$C20)>0
This works for one range as a conditional format but there aren't enough options to do the 7 days.
View 9 Replies
View Related
Dec 19, 2008
I have a worksheet which contains START TIME in column A, then TIME USAGE in column B and END TIME in column C. User enters start time, followed by the number of time usage in minutes, how could i possibly display the end time automatically in this scenario? how do you add the entered time usage to the start time to display the end time? Say if I enter 1:00 AM at start time and 00:15 minutes on time usage, how can 1:15 AM be displayed on the end time automatically?
View 2 Replies
View Related
Jun 22, 2014
I 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 Related
Mar 12, 2007
how to combine 2 conditional formats?
I'm trying to shade every other row grey so its readable with this:
=MOD(ROW(),2)
But, at the same time, would like to highlight upcoming expiry dates with this:
=AND(A2-TODAY()>=0,A2-TODAY()<=30)
View 14 Replies
View Related
Mar 13, 2007
I 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 Related
Aug 19, 2009
I am trying to create an Excel spread sheet that keeps track of employees safety tickets and the dates they expire. Is there way to have a cell highlight to a color warning me 30 days prior to the due date listed?
Eg. John has first aid and expires on 11/15/2009. I'd like the cell that has this date to highlight red on 10/15/2009.
I have messed around with the conditional formatting and know how to format the cells color and font et., but I can not figure out the formula to use to get my results.
I am using Microsoft Office Excel 2003 (MS Office Standard)(SP3)
View 12 Replies
View Related
Jul 5, 2013
I'm trying to put cemetery records in a simple, sortable table, names and dates. The problem is that for some entries I have a full date of birth, death, or burial (e.g. 5/12/1892) and for others only a year (e.g. 1892). Is it possible to sort these? All I can get is the just years in order then the full dates at the end. My data look sort of like this:
Smith John 1892 1/1/1940 2/2/1892
Johnson Sarah 3/12/1900 1880 3/10/1900
One thing I don't know is what number format to use (text, long date, short date....) Sometimes the date I type changes after I type it (like 1892 became March 6, 1905) and sometimes it doesn't even though the cell is the same format.
View 3 Replies
View Related
