hour format

Guys,
What about the hour format.

I have 93000 or 181500

and I need it to be

9:30 or 18:15

Thanks

and I really tried to find a solution before posting the message

--
Message posted from http://www.ExcelForum.com

0
4/22/2004 7:57:10 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
407 Views

Similar Articles

[PageSpeed] 34

Hi
try the following formula
=TIME(LEFT(A1,1+LEN(A1)=6),MID(A1,2+LEN(A1)=6,2,RIGHT(A1,2))

-- 
Regards
Frank Kabel
Frankfurt, Germany


> Guys,
> What about the hour format.
> 
> I have 93000 or 181500
> 
> and I need it to be
> 
> 9:30 or 18:15
> 
> Thanks
> 
> and I really tried to find a solution before posting the message.
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
0
frank.kabel (11126)
4/22/2004 8:25:43 PM
One way using a help column assuming that your times when less than 10:00
has 5 digits and when
greater has 6 digits

Assume they start in A1

=(INT(A1/10000)+MOD(A1/100,100)/60)/24

copy down, paste special as values in place and format as hh:mm
if you can have numbers like 930 or 1815 as well you can use

=IF(LEN(A1)<=4,(INT(A1/100)+MOD(A1,100)/60)/24,(INT(A1/10000)+MOD(A1/100,100
)/60)/24)

note that it important to format as time or else you will get decimal values

-- 

Regards,

Peo Sjoblom

"galin >" <<galin.154r58@excelforum-nospam.com> wrote in message
news:galin.154r58@excelforum-nospam.com...
> Guys,
> What about the hour format.
>
> I have 93000 or 181500
>
> and I need it to be
>
> 9:30 or 18:15
>
> Thanks
>
> and I really tried to find a solution before posting the message.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
terre081 (3244)
4/22/2004 8:32:44 PM
I'm sorry,

I can't figure it out. Maybe I am not doing it right.

Let me figth , we'll see what happens.

Gali

--
Message posted from http://www.ExcelForum.com

0
4/22/2004 9:17:13 PM
galin wrote:
> *I'm sorry,
> 
> I can't figure it out. Maybe I am not doing it right.
> 
> Let me figth , we'll see what happens.
> 
> Galin * 


I have attached a sample here


Regards,

Peo Sjoblo

--
Message posted from http://www.ExcelForum.com

0
4/22/2004 10:02:35 PM
Oops!

http://terre08.home.mindspring.com/test_for_time.xls

right click link and select save target..

-- 

Regards,

Peo Sjoblom

"terre08 >" <<terre08.154wy9@excelforum-nospam.com> wrote in message
news:terre08.154wy9@excelforum-nospam.com...
> galin wrote:
> > *I'm sorry,
> >
> > I can't figure it out. Maybe I am not doing it right.
> >
> > Let me figth , we'll see what happens.
> >
> > Galin *
>
>
> I have attached a sample here
>
>
> Regards,
>
> Peo Sjoblom
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
terre081 (3244)
4/22/2004 10:24:47 PM
Thanks for the help Peo,

You have been magnificient.

I am fine with my excel spreadsheets now, but I have encountere
another problem now. When  I import  the table with the hours time fro
Excel to Access I am losing the format. Basically I need to have Acces
run query on the hour table in format understandable for the staff an
also looking good in printed report form.

Do you have any suggestions?

Thanks ,Galin

Don't feel obligated to respond me. You've done enoug

--
Message posted from http://www.ExcelForum.com

0
4/23/2004 5:25:48 AM
Reply:

Similar Artilces:

Setup Account Format
I have installed Microsoft Dynamic GP, working with the Demo data come with it. I’m logged with SA user and I tried to change the account format and received this message: You cannot change the Account Format because there are other users in this company. Please help me Thanks jvrodriguez@infomedika.com Sometime users can get "stranded" in Dynamics. Go to Tools - Utilities - System-User Activity. Look for users there beside the 'sa' user. You can also try rebooting the SQL server to clear temp tables. -- www.fmtconsultants.com "Valentin Puerto Rico&qu...

Formatting email addresses
I keep several spread sheets of club membership, etc, and need to concentrate email addresses, but I do not want them to be hyperlinks. I have formatted the entire column as Text, but sometimes when I add a line, the formatting rebounds to some sort of default. I never want any hyperlinks in my worksheets EVER. Can someone please advise? TIA!! -- Regards, P D Sterling Dallas TX Try this: From the Excel main menu: <tools><options><spelling tab> Click the [AutoCorrect Options...] button Select the AutFormat as you Type tab UNcheck: Internet and network paths with hyper...

Excel 2003 convert hours from GMT to PST??
Hello! I have a huge log file that has time in hh:mm:ss GMT and I want to convert those time to PST. I need to subtract 8 hours from the GMT time, but I cannot figure out how to do it. Any ideas? Thank you! Greg Hill I would use a help column, assume the times are truly numeric and not text =A1-8/24 where A1 holds the GMT, you might get a pseudo date so format result as hh:mm another way (less attractive if you make a mistake since there are no audit trails except undo) put =8/24 in a cell, format the cell as time,copy it, select all dates, do edit>paste special and select ...

Retaining format while file conversion
Hi! I have few text files with more than 6000 records. There are columns in which the data stats with 0 digit for ex. 021996000058. While I convert this .txt file into .xls file the leading zero disappers. for ex. the above given number becomes 21996000058. Is there any way by which I can retain the data with same format as in the text file? Please help! Jayshree Hi When you open the txt file in Excel it should launch the Text Import Wizard which lets you assign which fields are text fields as well as delimiter/fixed width etc. -- Andy. "Jayshree" <jayshree.dholakia@uga...

If you accidently save a file in another format, is there any way.
I can't open an EXCEL file. I get an error message which says "the file is in an unrecognizable format". The only thing I can think of is when I saved it the last time, I didn't make sure it was saved in EXCEL. Maybe I saved it in another format? If so, is there ANY way to retrieve the file? Jay To where did you save it and from where are you opening it? Files saved directly to or opened from removable media can become corrupt. Otherwise........... Generally the case is that you have opened the file in Word and saved it. If so, it is now a Word *.doc file You ca...

time format #6
Cell format for time is set at hh:mm but cell reference shows hh:mm:ss. It must be in hh:mm format to export to another system. Any ideas how to remove the seconds part of the format or convert to text format without having to retype all entries? You could round to the nearest minute with: =ROUND(A1/(1/24/60),0)*(1/24/60) and then copy the formula down. Now copy this and paste special > value over column A and reformat as hh:mm. HTH Jason Atlanta, GA >-----Original Message----- >Cell format for time is set at hh:mm but cell reference >shows hh:mm:ss. It must be in hh:...

Hours to minutes
Can someone tell how to convert hours to minutes? Hi Brian with the number of hours in A1 in B1 type =A1*60 Cheers JulieD "Brian S." <BrianS@discussions.microsoft.com> wrote in message news:EA301D19-4859-4798-8087-59E0F657E267@microsoft.com... > Can someone tell how to convert hours to minutes? ...

Custom Formatting
Hi, I need to format text and numbers, but I keep messing up. For example, I want to format A12345678901 so that it will appear as A123-4567-8901 Please help? Start with 12345678901 in a cell. Then: Format > Cells > Custom > "A"000-0000-0000 Don't manually enter the "A", let the formatting do it. -- Gary''s Student - gsnu200779 "cliff" wrote: > Hi, I need to format text and numbers, but I keep messing up. > For example, I want to format > A12345678901 so that it will appear as > A123-4567-8901 > > Please help? ...

Conditional Formatting
I have a sheet that already has conditonal format where every 3rd row is highlighted green (the cond. format formula is =MOD(ROW(),3)=1 ). Now I want to add another condition where the #N/A is not visible. Its OK for all the rows that are not highlighted but it still shows the error in the rows still highlighted. how can i work around this? You can avoid the problem with =ISNA(). for example, rather than: =VLOOKUP(A1,D1:E10,2) use: =IF(ISNA(VLOOKUP(A1,D1:E10,2)),"",VLOOKUP(A1,D1:E10,2)) -- Gary''s Student - gsnu200909 "Panchi" wrote: >...

Name Format
Is it a way to change a name format from "First_ name Middle_ Initial, Last_name" to "Last_name, First_name Middle_initial"? For example, from "John A. Smith" to "Smith, John A". One way: Assuming your list is in column A Highlight the list Data>Text to Columns>Delimited> Other and enter a space>Destination>B1 Now you have seperated the names into 3 parts In cell E1 enter , Now the formula in F1is =D1&$E$1&" "&B1&" "&C1 Copy the formula down HTH "NH" wrote: > Is it a way to change ...

How can I conditionally format in excel for >3 conditions
There are a possible 5 different conditions which I wish to use to determine the colour of a given cell. Each condition should result in a different colour. Using the "wizard" I get a maximum of 3 colours. Is there a slick way around this limitation? What's the criteria for all your scenarios? I mean, chances are, using a =if() with an or() or and and() will do just fine, 1 condition. Danny On Mon, 11 Jul 2005 05:21:03 -0700, Jimbo <Jimbo@discussions.microsoft.com> wrote: > There are a possible 5 different conditions which I wish to use to > de...

Problems saving docx to doc97 or rtf formats programmatically
Hello, I'm relatively new to .NET and Office-Development, so I apologize in advance if my questions sound newbish or if I'm stealing anybody's time. I am trying to convert Office 2007 (.docx) files to the old 97-2003 format (.doc) by using the conversion functionality from Word. I used the code example for ExportAsFixedFormat from msdn (http://msdn.microsoft.com/en-us/library/bb412305.aspx) and modified it to use the saveAs method from the Document object (http://msdn.microsoft.com/en-us/library/bb221597.aspx). As long as i convert pdf or xps files everything ru...

adding minutes and hours
Hi, My apologies if this has already been posted, but I am looking for a quick and easy way to add minutes and hours together ie. I have a row in excel for overtime, the user enters 1.20 , .40, 2.10, 1.10, .35 these five entries will be the overtinme for monday to friday - is there any easy way to add them in hours and minutes? (total for the week = 5.55) Many thanks, Papa Hi 1. format the entry cells as 'Date' so that the user will enter '1:20', '0:40', etc. 2. In the target cell just add them (e.g. =A1+B1+C1....) 3. Format the target cell with the for...

Conditional chart formating
I need to format my charts so the color of the data points change depending on the value amount. My tabels are already set up and cannot be change or have anything new added due to the amount and delicacy of data. Is there a macro I can use. Perhaps a formula I can put somewhere? Hi, Ed Ferrero has a vba example. It's for a column chart but the code will be very similar. Instead of interior fill you will be doing marker stuff. eg: .MarkerBackgroundColorIndex = 50 .MarkerForegroundColorIndex = 50 Conditional Chart Formatting example. http://www.edferrero.com/charti...

Keep Pivot Table Chart Format unchanged
I want to keep changes to format i performed in a pivot chart, that would not be affected upon refresh table. thanks\ This is a known bug that requires vba code to re-apply formatting. XL2000: Changing a PivotChart Removes Series Formatting http://support.microsoft.com/?id=215904 Cheers Andy Nir wrote: > I want to keep changes to format i performed in a pivot chart, that would not > be affected upon refresh table. > > thanks\ > -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info thanks Andy, does MS supply any add-in for or just record it on my loc...

Help with conditional formating
Greetins, I have a workbook that in column A the cell contents are populated with dates, let's say A1= 07/01/07, A2=07/02/07, etc. I would like to do some conditional formating that examines the date in each row, and if the value is found to be either "Saturday" or "Sunday" (day of week) the whole respective row will turn bold black and grayed background. I already know you can use the function 'text(a1,"dddd") to get the day, it is the rest I am lost on. Thanks for any help. -lumpjaw Hi Provided you have the Analysis Toolpak loaded, Tools&...

One hour before now
How do I specify an hour ago in code (using the Now() function)? Robert Robert wrote: > How do I specify an hour ago in code (using the Now() function)? > > Robert =DateAdd("h", -1, Now()) -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com Thank you. "Rick Brandt" <rickbrandt2@hotmail.com> wrote in message news:wBH7j.80123$Um6.48010@newssvr12.news.prodigy.net... > Robert wrote: >> How do I specify an hour ago in code (using the Now() function)? >> >> Robert > > =DateAdd(...

Format changes
When I send Emails to clients with a spreadsheet attached the files are changed from .xls to .dat. Why does this happen? If MS Outlook is your email client............... Most likely because you are sending in "Rich Text Format" Change to "Plain Text" and the problem will(should) go away. Gord Dibben Excel MVP On Mon, 28 Nov 2005 07:51:02 -0800, "kym_596" <kym_596@discussions.microsoft.com> wrote: >When I send Emails to clients with a spreadsheet attached the files are >changed from .xls to .dat. Why does this happen? ...

Formatting Phone Numbers
I am re-posting this question with the hopes of getting some direction. All of our users are using the Outlook client of MSCRM. We are trying to be consistent in the way that we format our phone numbers, addresses, etc. in CRM. When some of our users enter a phone number like 717-123-4567, CRM will change it to (717) 123-4567 after the record is saved. This only happens on select computers. Does anyone have an idea what is affecting the formatting in CRM and how do I change it? Thanks, Tom Hello Tom CRM does not have the ability to autoformat phone numbers so there must be something ...

Conditional Formatting
Dear All, I am preparing a compartive statement i want to highlight the max and min a group of cells. quote-1 quote-2 quote-3 100 150 200 like this upto quote-6 I want to highlight the number max of 6 as red and min of 6 as yellow. Please do the needful . 1. Select the cell/Range (say A2:F2). Please note that the cell reference A2 mentioned in the formula is the active cell in the selection. Active cell will have a white background even after selection 2. From menu Format>Conditional Formatting> 3. For Condition1>Select 'Formula Is' and enter the below ...

Overallocated resource under 8 hours
I have a resource in two seperate plans - both tasks are under 8 hours in total and the resource usage shows it in RED. I understand for small tasks to change the start and finish times so they don't overlap from the default of 08:00. In this case though I have resource doing long tasks in two seperate projects. I can see them at 7.6 hours in Red - but the tasks are months long - and you cannot change the start time of one particular day during this task. Any ideas how to rectify this? Burnley01 -- One of the leveling actions I occasionally recommend is to "live in th...

Edit Field
I often record a macro to repeat often-used keystrokes, but today I found a keystroke that couldn't be recorded. I use Tools, Macros, Record a new Macro and assign it to a toolbar. In my document, I have some cross-references to bookmarks. Some bookmarks begin with a lower-case letter (due to the context in which they are used), but this may be changed in the cross-reference by right-clicking on the field, Edit field, Format - First capital. However, when trying to capture this sequence in a macro recording, the right-click function is not available. Is there an alternative ...

subtract half an hour from hours and minutes
could somebody please point me in the right direction here. Im tryin subtract 30 minutes form a cell containing a number of hours an minutes formatted using [h}:mm custom cell forma -- MajorGeneralDe ----------------------------------------------------------------------- MajorGeneralDes's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=370 View this thread: http://www.excelforum.com/showthread.php?threadid=31945 Any of these will work: =A1-"00:30" =A1-TIME(,30,) =A1-1/24/2 HTH Jason Atlanta, GA >-----Original Message----- > >could somebod...

Formatting several worksheets the same at one time
I am creating a workbook with one summary page and several back-up contributing worksheets. How do I create the contributing worksheets so they formatted exactly the same and maintain the summary worksheet's formatting? Also, how can I lock certain areas of a workbook without passcoding it so someone else can only enter information where I want them to enter it? "Mary R" <anonymous@discussions.microsoft.com> wrote in message news:ec4f01c43d11$a504f380$a601280a@phx.gbl... > I am creating a workbook with one summary page and several > back-up contributing worksh...

Conditional formatting #20
I have a row of dates that are formatted to show the day of the week, i.e., 'Wed, Fri, etc. When a cell in the first row is Wed, I want the cell below it to appear in purple. How? I have Excel 97. Hi! Assume the dates are in the range A1:G1. Select the range A2:G2. Goto Format>Conditional Formatting. Select Formula Is and enter this formula: =WEEKDAY(A1)=4 Click the Format button and select the style(s) you want. OK out Biff >-----Original Message----- >I have a row of dates that are formatted to show the day of the week, >i.e., 'Wed, Fri, etc. > >When a cel...