Cell Format #11

I have a situation I do not understand, maybe someone can give me some
direction. I have received an invoice from one of our suppliers via email.
They provide a way to translate there invoice into a comma separated value
file.

When I translate to excel I get a column with  756:00:00   in a cell. If I
look at the bar at the top of the screen I have the value" 1/31/1900  12:00
PM  "

The result I am trying to get is numeric 756 .

Does anyone have a suggestion on how I can get to the result I want??

Thank you in advance.
Greg


0
gfjesky (14)
6/11/2008 2:26:00 PM
excel 39879 articles. 2 followers. Follow

10 Replies
552 Views

Similar Articles

[PageSpeed] 2

Bonsour=AE Greg  avec ferveur  ;o))) vous nous disiez :=20

> When I translate to excel I get a column with  756:00:00   in a cell.
> If I look at the bar at the top of the screen I have the value"
> 1/31/1900  12:00 PM  "
>=20
> The result I am trying to get is numeric 756 .

divide by 24
format cell : standard

or multiply by "1:"


--=20
--=20
@+
;o)))

0
6/11/2008 3:50:35 PM
Enter 24 in an unused cell then select it again and copy it.  Now select the 
756:00:00 cell and Paste Special > Multiply.  You may or may not have to 
reformat as General or number -  I didn't have to

-- 
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"Greg" <gfjesky@bellsouth.net> wrote in message 
news:_5R3k.3021$Xe.579@bignews1.bellsouth.net...
>I have a situation I do not understand, maybe someone can give me some
> direction. I have received an invoice from one of our suppliers via email.
> They provide a way to translate there invoice into a comma separated value
> file.
>
> When I translate to excel I get a column with  756:00:00   in a cell. If I
> look at the bar at the top of the screen I have the value" 1/31/1900 
> 12:00
> PM  "
>
> The result I am trying to get is numeric 756 .
>
> Does anyone have a suggestion on how I can get to the result I want??
>
> Thank you in advance.
> Greg
>
>
> 


0
sandymann2 (1054)
6/11/2008 3:50:42 PM
Sandy and "Mais qui est Paul",

Thank you both I have completed the assignment through your advice. But, why
does this work??

Greg

"Sandy Mann" <sandymann2@mailinator.com> wrote in message
news:%23iVHrr9yIHA.5520@TK2MSFTNGP06.phx.gbl...
> Enter 24 in an unused cell then select it again and copy it.  Now select
the
> 756:00:00 cell and Paste Special > Multiply.  You may or may not have to
> reformat as General or number -  I didn't have to
>
> -- 
> HTH
>
> Sandy
> In Perth, the ancient capital of Scotland
> and the crowning place of kings
>
> sandymann2@mailinator.com
> Replace @mailinator.com with @tiscali.co.uk
>
>
> "Greg" <gfjesky@bellsouth.net> wrote in message
> news:_5R3k.3021$Xe.579@bignews1.bellsouth.net...
> >I have a situation I do not understand, maybe someone can give me some
> > direction. I have received an invoice from one of our suppliers via
email.
> > They provide a way to translate there invoice into a comma separated
value
> > file.
> >
> > When I translate to excel I get a column with  756:00:00   in a cell. If
I
> > look at the bar at the top of the screen I have the value" 1/31/1900
> > 12:00
> > PM  "
> >
> > The result I am trying to get is numeric 756 .
> >
> > Does anyone have a suggestion on how I can get to the result I want??
> >
> > Thank you in advance.
> > Greg
> >
> >
> >
>
>


0
gfjesky (14)
6/11/2008 6:12:09 PM
Hi "Well  who is Paul"
I'm curious to know what is " Bonsour " is it Bonjour or Bonsoir ? Just 
curious
Regards
Cimjet
"Mais qui est Paul" <Excel-addict@mpfe.fr> wrote in message 
news:eEeF2r9yIHA.4476@TK2MSFTNGP06.phx.gbl...
Bonsour� Greg  avec ferveur  ;o))) vous nous disiez :

> When I translate to excel I get a column with  756:00:00   in a cell.
> If I look at the bar at the top of the screen I have the value"
> 1/31/1900  12:00 PM  "
>
> The result I am trying to get is numeric 756 .

divide by 24
format cell : standard

or multiply by "1:"


-- 
-- 
@+
;o)))

0
300 (30)
6/11/2008 6:18:16 PM
Dates in Excel are just numbers, a count of the days since 1900.  Times are 
a fraction of a day so being as a day has 24 hours 12 Noon is 12/24 = 0.5, 
6AM is 0.25 etc.  The time is formatted to look like a time but this is just 
a mask over the top of the real value that is held in the cell which is 
still the decimal number.  Multiplying by 24 changes to fraction into a 
number.  ie 6AM is 0.25*24 = 6

-- 
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"Greg" <gfjesky@bellsouth.net> wrote in message 
news:LqU3k.3761$s77.1226@bignews3.bellsouth.net...
> Sandy and "Mais qui est Paul",
>
> Thank you both I have completed the assignment through your advice. But, 
> why
> does this work??
>
> Greg
>
> "Sandy Mann" <sandymann2@mailinator.com> wrote in message
> news:%23iVHrr9yIHA.5520@TK2MSFTNGP06.phx.gbl...
>> Enter 24 in an unused cell then select it again and copy it.  Now select
> the
>> 756:00:00 cell and Paste Special > Multiply.  You may or may not have to
>> reformat as General or number -  I didn't have to
>>
>> -- 
>> HTH
>>
>> Sandy
>> In Perth, the ancient capital of Scotland
>> and the crowning place of kings
>>
>> sandymann2@mailinator.com
>> Replace @mailinator.com with @tiscali.co.uk
>>
>>
>> "Greg" <gfjesky@bellsouth.net> wrote in message
>> news:_5R3k.3021$Xe.579@bignews1.bellsouth.net...
>> >I have a situation I do not understand, maybe someone can give me some
>> > direction. I have received an invoice from one of our suppliers via
> email.
>> > They provide a way to translate there invoice into a comma separated
> value
>> > file.
>> >
>> > When I translate to excel I get a column with  756:00:00   in a cell. 
>> > If
> I
>> > look at the bar at the top of the screen I have the value" 1/31/1900
>> > 12:00
>> > PM  "
>> >
>> > The result I am trying to get is numeric 756 .
>> >
>> > Does anyone have a suggestion on how I can get to the result I want??
>> >
>> > Thank you in advance.
>> > Greg
>> >
>> >
>> >
>>
>>
>
>
> 


0
sandymann2 (1054)
6/11/2008 6:20:16 PM
Bonsour=AE Greg  avec ferveur  ;o))) vous nous disiez :=20

> Thank you both I have completed the assignment through your advice.
> But, why does this work??

because in EXCEL dates and times are a special representation of a =
number.=20
the whole party corresponds to a date=20
the decimal part is the fraction of day=20
an hour =3D 1 day divided by 24
"1:00" =3D 1/24
--=20
--=20
@+
;o)))

0
6/11/2008 6:23:59 PM
Bonsour=AE Cimjet  avec ferveur  ;o))) vous nous disiez :=20

> Hi "Well  who is Paul"
> I'm curious to know what is " Bonsour " is it Bonjour or Bonsoir ?
> Just curious
> Regards
> Cimjet

idiomatic joke ;o)))=20
A french entertainer said "Bonjoir"=A9

Due to the UTC gap everyone is free to choose!
Date: Wed, 11 Jun 2008 17:50:35 +0200
Date: Wed, 11 Jun 2008 14:18:16 -0400

--=20
@+
;o)))
Laughter makes a man!=20
Fran=E7ois Rabelais
148?- 1553

0
6/11/2008 8:09:33 PM
Thanks Paul for your reply.
Bonsoir
"Mais qui est Paul" <Excel-addict@mpfe.fr> wrote in message 
news:u3Wqi8$yIHA.4772@TK2MSFTNGP03.phx.gbl...
Bonsour� Cimjet  avec ferveur  ;o))) vous nous disiez :

> Hi "Well  who is Paul"
> I'm curious to know what is " Bonsour " is it Bonjour or Bonsoir ?
> Just curious
> Regards
> Cimjet

idiomatic joke ;o)))
A french entertainer said "Bonjoir"�

Due to the UTC gap everyone is free to choose!
Date: Wed, 11 Jun 2008 17:50:35 +0200
Date: Wed, 11 Jun 2008 14:18:16 -0400

-- 
@+
;o)))
Laughter makes a man!
Fran�ois Rabelais
148?- 1553

0
300 (30)
6/12/2008 12:40:43 AM
Greg,

Click on the cell in question and hit alt 1 to bring up the cell format box 
then click number, general or text, depending on the way you want it 
formated, right now it looks as though the cell is formated as a date.

Mike


"Greg" <gfjesky@bellsouth.net> wrote in message 
news:_5R3k.3021$Xe.579@bignews1.bellsouth.net...
>I have a situation I do not understand, maybe someone can give me some
> direction. I have received an invoice from one of our suppliers via email.
> They provide a way to translate there invoice into a comma separated value
> file.
>
> When I translate to excel I get a column with  756:00:00   in a cell. If I
> look at the bar at the top of the screen I have the value" 1/31/1900 
> 12:00
> PM  "
>
> The result I am trying to get is numeric 756 .
>
> Does anyone have a suggestion on how I can get to the result I want??
>
> Thank you in advance.
> Greg
>
> 

0
mike4071 (6)
6/17/2008 4:39:31 AM
Changing the formatting will not change the underlying value that is being 
held in the cell.  Just changing the format to General will result in the 
756:00:00 that the OP sees displaying as the real value of 31.5

-- 
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"Mike Kennedy" <mike@the3kennedys.com> wrote in message 
news:1CE7B982-76B2-4089-BC92-888995D89F56@microsoft.com...
> Greg,
>
> Click on the cell in question and hit alt 1 to bring up the cell format 
> box then click number, general or text, depending on the way you want it 
> formated, right now it looks as though the cell is formated as a date.
>
> Mike
>
>
> "Greg" <gfjesky@bellsouth.net> wrote in message 
> news:_5R3k.3021$Xe.579@bignews1.bellsouth.net...
>>I have a situation I do not understand, maybe someone can give me some
>> direction. I have received an invoice from one of our suppliers via 
>> email.
>> They provide a way to translate there invoice into a comma separated 
>> value
>> file.
>>
>> When I translate to excel I get a column with  756:00:00   in a cell. If 
>> I
>> look at the bar at the top of the screen I have the value" 1/31/1900 
>> 12:00
>> PM  "
>>
>> The result I am trying to get is numeric 756 .
>>
>> Does anyone have a suggestion on how I can get to the result I want??
>>
>> Thank you in advance.
>> Greg
>>
>>
>
> 


0
sandymann2 (1054)
6/17/2008 9:01:06 AM
Reply:

Similar Artilces:

Email Signature 11-17-09
When I reply to an email, my signature superimposes over the sender's message. What am I doing wrong? ...

Click in cell w/ formula and get colors in referenced cells
Hi, When you click in a cell with a formula, Excel will then put color around the cells that are referenced. Somehow I turned that feature off. Now I do not get colors in th other cells. Where is that option to turn it back on???? I've looke and looked but I just can't find that option. Thanks for the help -- albea ----------------------------------------------------------------------- albean's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2875 View this thread: http://www.excelforum.com/showthread.php?threadid=48436 ...

test 05-11-10
hiiiiiiiiiiiiii ...

Miscellaneous words appear in Excel 2002 cells
I am working on an Excell 2002 workbook and when I put my cursor in a cell , spurious words get entered into the cell e.g. "and thee the and ...." is one example. I have cable modem and it feels like "someone" has taken control of my PC - by the way I have also noticed this hapening in MS Word but is not as bad as Excel - can some one help. thanks KK Have you enabled speech tools? Click Tools / Speech and examine your options. /i. "KK" <anonymous@discussions.microsoft.com> wrote in message news:061d01c39d1c$36fc85f0$a601280a@phx.gbl... > I am w...

First row in Selection range (first index of a cell)
Hello, I have a problem with selecting first cel in selection Range or return an index of the first cell in Selection Cell. I have something like this: .................... Range1.Select "and here I want to Select the first range in selection Range1" ............. I there any special function of finding first cell in selection range or returning an index of the first cell?? Thanks for answet Marcin Maybe range1(1).select or range1.cells(1).select or range1.cells(1,1).select mar_male@wp.pl wrote: > > Hello, > I have a problem with selecting first cel in selection...

Avoid protected cell warning on BeforeDoubleClick WS event
I am creating an event scheduling worksheet. A grid is generated with days of the month x-axis and users y-axis. Where an event occurs this is logged by a hidden event ID in the appropriate day cell. In order to prevent this ID from being overwritten I protect the worksheet. What I am trying to do is have a worksheet DoubleClick event which either. 1) Captures the event ID in the underlying cell, and opens a custom form for editing the event, or 2) Recognises that no event exists and opens a custom form for logging a new event. What I have tried is to unprotect the workshee...

formating color
hi i have a formating question. i have cells with number choices i them. for example the cell looks like this. 100, 105, 123, 041, 514, 455 now i want to go back and select one of these. i wanted to highligh the one that was chosen. but it will not let me. is there a way i ca do this? it will only allow me to highlight the entire cell, not jus parts of it. thank you in advanc -- RAPPE ----------------------------------------------------------------------- RAPPEL's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3624 View this thread: http://www.excelforum.com...

Conditional format #12
Hi. I am trying to set a conditional format. If NETWORKDAYS(A1,TODAY())> 3 I would like the cell to format with red shading. How do is set the formula in the condtional statement? I tried ="NETWORKDAYS(A1,TODAY())>3" , but this doesn't seem to work. Thanks, Mike. Hi Mike! It doesn't work because the NETWORKDAYS function is in another workbook (the Analysis ToolPak add-in). You can use a workaround by putting your condition in a separate cell (eg B1) and then referring to that cell: B1 =NETWORKDAYS(A1,TODAY()) Then your conditional format for A1 becomes:...

Formatting
I received a pub doc from a third party. It was formatted as a 11x16 size. Once I decreased it to 8.5x11 and view the doc under 'print preview' the entirety of the image did not view. Basically, the image didn't shrink with the size. Any input? Patty wrote: > I received a pub doc from a third party. It was formatted as a 11x16 size. > Once I decreased it to 8.5x11 and view the doc under 'print preview' the > entirety of the image did not view. Basically, the image didn't shrink with > the size. Any input? Publisher doesn't resize the contents o...

How can I customize the Filter Drop down on the formatting toolbar
I want to clear everything out of it, except the customized filters I have created. Can i do that? Project > Filter by > More Filters, then select each one, Edit, and remove the checkbox for Show in Menu. - Andrew Lavinsky Blog: http://blogs.catapultsystems.com/epm > I want to clear everything out of it, except the customized filters I > have created. Can i do that? > I've tried that - it doesn't affect that drop down filter list - whether they are checked or not. "Andrew Lavinsky" wrote: > Project > Filter by > Mo...

Where is the Keep Text Formatting feature located in Word 07
I believe this Keep Text Formatting feature might be what I need, but I have been unable to locate exactly where it is located in Word 2007. I'm trying to rid a Word document sent to me of tables, text boxes, graphics and all other document formatting, while retaining the document's text content. It is unimportant to me whether the text formatting is retained or not. Thanks. Are you referring to a Keep Text Formatting feature in an earlier version of Word? I wonder whether what you're looking for is "Paste Unformatted," since you seem to be saying you _don...

How do I change the row/column format in a macro to beyond letters?
Hi all, I recorded a very long VBA macro in Excel and the index of ranges is in letter format, for example, "M14:M19", etc. Now I am going to run this macro programmatically and automatically in a for loop and expand it from the left to the right so I want to change the "M" in the above example automatically. But after 26 letters, there will be AA, AB, etc. which is really hard to program. Is there a way to adapt the recorded macro (by changing as little as possible) to more than 26 letters. I really want to change as little as possible because I spent lots of time re...

Sorting #11
How do you sort a list with more than three sort keys? First sort on least significant key(s) Then sort on most significant keys HTH -- AP "Fran" <fpeale@comcast.net> a �crit dans le message de news: %23pS%239BXeGHA.1856@TK2MSFTNGP03.phx.gbl... > How do you sort a list with more than three sort keys? > You need a helper column. In this should be a formula concatenating all the fields to be sorted. =A1&B1&C1 ... This will sort only in ascending order. You will need to include the value of dates (not the actual dates) - &VALUE(A1) or &...

Formatting multiple object types in output from a function?
I'm stumped on this one, and have searched and searched for a solution or how-to with no luck. Perhaps it just can't be done. I have a function that may or may not return objects of multiple types. I've setup the type and formatting information for each of the individual types in format.ps1xml files, and when only 1 type is returned by the function the formatting works as I would expect. However, if two or more types are returned by the function, only the formatting for the first object type is applied and then the other types are displayed however PoSH deems ap...

Conditional Format #20
I see that I can only use 3 conditional formats. I need a few more than 3 is there any way to do this. Kevin Try this free add-in on Bob Phillips' website: http://www.xldynamic.com/source/xld.CFPlus.Download.html Another possibility (albeit limited) is to combine your conditional formats with custom formatting. See J.E. McGimpsey's site for an example: http://www.mcgimpsey.com/excel/conditional6.html HTH Jason Atlanta, GA >-----Original Message----- >I see that I can only use 3 conditional formats. I need a few more than 3 is >there any way to do this. > >Kev...

Sum of a cell in all worksheets?
I usually just use the search here and quickly find what I need, but can't seem to get it work this morning I am trying to get a sum of a certain cell in all the sheets in workbook. For example of what I mean, Sum(all worksheets, d64). Ca someone possibly help me out Thanks Michae Hi Michael, Look up 3D in your Excel Help. Surely you do not want to include the same sheet. if sheet2 is your second sheet tab and "sheet 24" is the last tab. =SUM(sheet2:'sheet 24'!C14) --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: htt...

Insert a graphic in a cell
I'd like to be able to enbed a logo in a cell. Not possible. Graphic objects reside on the drawing layer "above" the cells. Cells can contain formulae or values only. Workaround. Insert your graphic, and size it so that it exactly covers the cell. Right-click it, choosing Format Picture. In the Properties tab of the Format Picture dialog, choose the Move and Size with cells radio button. In article <F253C07B-E71A-445E-B612-0189187A09D9@microsoft.com>, Pete_Escher fan <Pete_Escher fan@discussions.microsoft.com> wrote: > I'd like to be able to enbed a...

can one cell contain more than one independent number
A cell can contain several numbers, separated by space characters or line breaks (Alt+Enter). However, if you want to perform calculations on the numbers, it's best to keep them in separate cells. John K wrote: -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html there's also the option a using matrix formula (list of data) for example you may enter ={1;2;3} in a single cell or selection 'and validate wih CTRL+SHIFT+ENTER but the manipulation of this kinda of formula need to read more about it... ...

Ignore Blank Cells
I update a workbook weekly in separate worksheets. My master worksheet links to the appropriate cells for the updated data. These are simple percentage numbers (not forumlas, etc.) and only need to be a one-on-one link. The problem I have is that if one of the cells is blank, it returns a 0 and I need to to stay blank. Any ideas? There are about 57,000 cells I am working with so I really don't want to have to update manually all blanks. Hi SEF, As long as you want to hide all zero values on a sheet you can use in excel 2003 From the Tools menu select Options On tab V...

Conditional formatting / blank cells
Hello, I need help with a Conditional Format. This is my worksheet. Row 4 A B C D E F G H I $200 $210 I want a conditional format in G4 that states if G4 is greater than or equal to F4 the fill colour is green. If G4 is less than F4, the fill colour is red. If G4 is blank, the fill colour is white. I've tried numerous combinations, but cannot seem to get this to work. Thanks torkattack. Test for the blank first. -- David Biddulph "torkattack" <torkattack@discussions.microsoft.com> wrote ...

Excel could not save all the data and formatting you recently added
Hi, One of our users sent me an Excel file of 6 MiB. It has 7 worksheets. Most of them have <100 rows and AH columns, one sheet has 13160 rows and AH columns. The large sheet has autofilter enabled, but no actual filtering is done. (yet) 4 columns have validation: they allow a list of values specified in a range somewhere else in the sheet. There is also conditional formatting. It takes >30 seconds to calculate the sheet, however there are no real calculations, just a few concatenated string. My first impression is that this is yet another example of Excel (ab)used as a database. The p...

Clipart will not scale with array of cells
I want to print out an array of cells, some of which contain clipart. When I try to scale up the array to fill the printed page, the clipart scales differently (the clipart objects move down on the page from their normal position within the cells). The artwork size does not change, nor its spacing; it's as though the clipart is reacting to a larger top margin. ...

PDF format pagination
I have several reports utilizing Landscape layout. I followed the Report Writers Guide from the SDK with the Width at 11 and Height 8.25 or smaller. However, when I export it to the PDF format, the layout shows up Portrait. How could I change this? The export is fine in Excel with the correct margin setup. Thanks. ...

CSV, Milliseconds default formatting of mm:ss.0
When I generate some CSV files I need to include milliseconds. When anyone opens the file in Excel (in my case part of Office 2003, SP2) the datetimes appear with a default formatting of mm:ss.0. This is frustrating and appears unprofessional on my part when clients open the file. They assume I have made some sort of mistake. I have already thought of generating using =DATE(blah)+TIME(blah)+millis/(24*60*60*1000) but I don't like the idea. How can I change the default format so that it would be something more sensible and my clients and I could avoid the drudgery of clicking on the...

Conditional formatting formula with multiple criteria
Hi all, Have been puzzling over this for a while now, and can't get it right! Using conditional formatting, I want to: If any of the cells in Column B are blank, and the cells in Column C are blank, and the date in Column D is less than todays date - 3 days, turn the cell bold and red Can someone please help? I have tried all different variations and just can't get it! Thanks, Kirstie Kirstie Your questions sound like you wish to pick up whether there is a blank ANYWHERE in column B AND (not or) ALL cells in column C are blank AND that the date in a single cell in colum...