Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert number to date format
I have an Excel file with a Time column formatted as a general number (e.g.
830, 1102, 805). I need to convert these numbers to a time format like 8:30, 11:02, and 8:05. I've tried =TEXT(a2,"h:mm"); however, it only returns a zero. These data will be imported into a time formatted field in Access and later used to determine the difference between schedule delivery time versus actual delivery time. Any assistance on how to convert this field would be very helpful. Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert number to date format
Lesley
Use =TIMEVALUE(IF(LEN(A3)=3,LEFT(A3,1)&":"&RIGHT(A3,2) ,LEFT(A3,2)&":"&RIGHT(A3,2))) And format as time -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England www.nickhodge.co.uk HIS "Lesley" wrote in message ... I have an Excel file with a Time column formatted as a general number (e.g. 830, 1102, 805). I need to convert these numbers to a time format like 8:30, 11:02, and 8:05. I've tried =TEXT(a2,"h:mm"); however, it only returns a zero. These data will be imported into a time formatted field in Access and later used to determine the difference between schedule delivery time versus actual delivery time. Any assistance on how to convert this field would be very helpful. Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert number to date format
A1=1436
in B1: =TIME(LEFT(A1,LEN(A1)-2),RIGHT(A1,2),0) B1 == 14:36 Format cell as hh:mm HTH "Lesley" wrote: I have an Excel file with a Time column formatted as a general number (e.g. 830, 1102, 805). I need to convert these numbers to a time format like 8:30, 11:02, and 8:05. I've tried =TEXT(a2,"h:mm"); however, it only returns a zero. These data will be imported into a time formatted field in Access and later used to determine the difference between schedule delivery time versus actual delivery time. Any assistance on how to convert this field would be very helpful. Thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert number to date format
hi Lesley
try it: =if(len(a2)<4,time(left(a2,1),right(a2,2),0),time( left(a2,2),right(a2,2),0)) hth regards from Brazil Marcelo "Lesley" escreveu: I have an Excel file with a Time column formatted as a general number (e.g. 830, 1102, 805). I need to convert these numbers to a time format like 8:30, 11:02, and 8:05. I've tried =TEXT(a2,"h:mm"); however, it only returns a zero. These data will be imported into a time formatted field in Access and later used to determine the difference between schedule delivery time versus actual delivery time. Any assistance on how to convert this field would be very helpful. Thanks! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert number to date format
830 turns to 8 hours 30 minutes?
=--TEXT(A1,"00\:00\:\0\0") Format as time. Just in case.... 830 turns to 8 minutes 30 seconds? =--TEXT(A1,"00\:00\:00") still format as time Lesley wrote: I have an Excel file with a Time column formatted as a general number (e.g. 830, 1102, 805). I need to convert these numbers to a time format like 8:30, 11:02, and 8:05. I've tried =TEXT(a2,"h:mm"); however, it only returns a zero. These data will be imported into a time formatted field in Access and later used to determine the difference between schedule delivery time versus actual delivery time. Any assistance on how to convert this field would be very helpful. Thanks! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
format a cell to accept a date or a whole number | Excel Discussion (Misc queries) | |||
Converting number or text to a Date Format | New Users to Excel | |||
How can I convert a date format to an ISO week format (in EXCEL)? | Excel Discussion (Misc queries) | |||
Convert degree from decimal number to the standard format (degre. | Excel Discussion (Misc queries) | |||
convert number to text and format it. | Excel Worksheet Functions |