Format numbers/time format

[Guest]

We have been getting an Excel file from a vendor that had numbers in a
General format. This worked great for our purposes (exporting to Access).

Example:
Actual contents of A1: 58.36
Display of A1: 58.36
This is what we want....

This time around the file has been sent to us and the numbers are not in
General format, but a Custom format that looks like this:

[>=0.0416666666666666][h]:mm:ss;[m]:ss

Actual contents of A1: 12:05:29 AM
Display of A1: 5:29
We want the display of 5:29 to be the real contents of the cell. Not the
time format that is currently there.

When we change the format to General, we get:

Contents of A1: 12:05:29 AM
Display of A1: 0.00380787
So, just changing it to General isn't the 'magic' that we need.

Any ideas?

BTW: The vendor is disavowing any knowledge of their actions! We are still
trying to work with them.

TIA!

 

[Dave Peterson]

The contents of that cell is gonna be 12:05:29 AM no matter what (unless you
change the format to text and type what you want). That's just the way excel
treats times.

You can display it in the cell anyway you want using the format you like.

If you want to use a helper cell that converts that time to a string, you could
use a formula like:

=TEXT(A1,"[>=0.0416666666666666][h]:mm:ss;[m]:ss")

We have been getting an Excel file from a vendor that had numbers in a
General format. This worked great for our purposes (exporting to Access).

Example:
Actual contents of A1: 58.36
Display of A1: 58.36
This is what we want....

This time around the file has been sent to us and the numbers are not in
General format, but a Custom format that looks like this:

[>=0.0416666666666666][h]:mm:ss;[m]:ss

Actual contents of A1: 12:05:29 AM
Display of A1: 5:29
We want the display of 5:29 to be the real contents of the cell. Not the
time format that is currently there.

When we change the format to General, we get:

Contents of A1: 12:05:29 AM
Display of A1: 0.00380787
So, just changing it to General isn't the 'magic' that we need.

Any ideas?

BTW: The vendor is disavowing any knowledge of their actions! We are still
trying to work with them.

TIA!

 

[Guest]

use a helper cell
=text(A1,"m:ss")
copy the helper cell and select A1 and paste special values
delete helper cell

this will give a text value without the hour.

are you sure this is what you want?
what do you want when it is 1:15:33?
is it going into access as time or text?

 

[Guest]

This is perfect! Thanks so much.

I understand that this seems like a VERY strange thing to do, but I'm
helping out someone and they have assured me this is what they want! Go
figure!

THANKS SO MUCH! I learned something very helpful today!

use a helper cell
=text(A1,"m:ss")
copy the helper cell and select A1 and paste special values
delete helper cell

this will give a text value without the hour.

are you sure this is what you want?
what do you want when it is 1:15:33?
is it going into access as time or text?

We have been getting an Excel file from a vendor that had numbers in a
General format. This worked great for our purposes (exporting to Access).

Example:
Actual contents of A1: 58.36
Display of A1: 58.36
This is what we want....

This time around the file has been sent to us and the numbers are not in
General format, but a Custom format that looks like this:

[>=0.0416666666666666][h]:mm:ss;[m]:ss

Actual contents of A1: 12:05:29 AM
Display of A1: 5:29
We want the display of 5:29 to be the real contents of the cell. Not the
time format that is currently there.

When we change the format to General, we get:

Contents of A1: 12:05:29 AM
Display of A1: 0.00380787
So, just changing it to General isn't the 'magic' that we need.

Any ideas?

BTW: The vendor is disavowing any knowledge of their actions! We are still
trying to work with them.

TIA!