Cell Format

[Greg]

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

 

[Mais qui est Paul]

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:"

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Greg]

Sandy and "Mais qui est Paul",

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

Greg

 

[Cimjet]

Hi "Well who is Paul"
I'm curious to know what is " Bonsour " is it Bonjour or Bonsoir ? Just
curious
Regards
Cimjet
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:"

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Mais qui est Paul]

Bonsour® Greg avec ferveur ;o))) vous nous disiez :

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.
the whole party corresponds to a date
the decimal part is the fraction of day
an hour = 1 day divided by 24
"1:00" = 1/24

 

[Mais qui est Paul]

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

 

[Cimjet]

Thanks Paul for your reply.
Bonsoir
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

 

[Mike Kennedy]

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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk