Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format numbers/time format
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format numbers/time format
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") Pam wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format numbers/time format
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? "Pam" wrote: 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format numbers/time format
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! "bj" wrote: 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? "Pam" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to convert decimal numbers to time format | Excel Worksheet Functions | |||
Custom Cell format to mimic time format | Excel Discussion (Misc queries) | |||
Convert Text numbers to time format | Excel Discussion (Misc queries) | |||
how do I format numbers for time with thousandths of a second? | Excel Discussion (Misc queries) | |||
entering numbers to display a time format | Excel Discussion (Misc queries) |