If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Time Formatting Question
Hi
I run Excel 2K I download various times from a mainframe. When the come down they look like this: 1:53:00 AM When I try a custom format to convert this to minutes I get the number 1 as a result. I use fortmat:cell:custom:m But, if I enclose the "m" in square brackets ( [m] ) then it converts the time to minutes which is what I want. I would like to know what the [ ] brackets do? Thanks |
#2
|
|||
|
|||
Time Formatting Question
When you formatted the times as "m" Excel evaluates just "m" as the MONTH.
That's why you got a displayed value of 1. Excel evaluated 1:53:00 AM as January 1, 1900 1:53:00 AM. So, m = 1 (for January). When you include the brackets [m] Excel then evaluates the "m" as minutes and the brackets keep the minutes from rolling over into hours at 60 minutes. The same thing happens and is easier to see what Excel is doing when dealing with times that span more than 24 hours. Enter 25:00 in a cell and make sure the format is h:mm. You'll see that Excel displays 1:00. The hours rolled over into days at 24 hours so Excel displays the remainder which is 1 hour (25 hours minus 24 hours). Now, change the format to [h]:mm and you'll now see that Excel displays 25:00. So, the brackets [ ] keep the unit of measure from rolling over to the next higher unit of measure. -- Biff Microsoft Excel MVP "John Calder" wrote in message ... Hi I run Excel 2K I download various times from a mainframe. When the come down they look like this: 1:53:00 AM When I try a custom format to convert this to minutes I get the number 1 as a result. I use fortmat:cell:custom:m But, if I enclose the "m" in square brackets ( [m] ) then it converts the time to minutes which is what I want. I would like to know what the [ ] brackets do? Thanks |
#3
|
|||
|
|||
Time Formatting Question
Thanks ! This was an excellent answer to my question. Well Done !
"T. Valko" wrote: When you formatted the times as "m" Excel evaluates just "m" as the MONTH. That's why you got a displayed value of 1. Excel evaluated 1:53:00 AM as January 1, 1900 1:53:00 AM. So, m = 1 (for January). When you include the brackets [m] Excel then evaluates the "m" as minutes and the brackets keep the minutes from rolling over into hours at 60 minutes. The same thing happens and is easier to see what Excel is doing when dealing with times that span more than 24 hours. Enter 25:00 in a cell and make sure the format is h:mm. You'll see that Excel displays 1:00. The hours rolled over into days at 24 hours so Excel displays the remainder which is 1 hour (25 hours minus 24 hours). Now, change the format to [h]:mm and you'll now see that Excel displays 25:00. So, the brackets [ ] keep the unit of measure from rolling over to the next higher unit of measure. -- Biff Microsoft Excel MVP "John Calder" wrote in message ... Hi I run Excel 2K I download various times from a mainframe. When the come down they look like this: 1:53:00 AM When I try a custom format to convert this to minutes I get the number 1 as a result. I use fortmat:cell:custom:m But, if I enclose the "m" in square brackets ( [m] ) then it converts the time to minutes which is what I want. I would like to know what the [ ] brackets do? Thanks . |
#4
|
|||
|
|||
Time Formatting Question
"John Calder" wrote in message ... Hi Say your time is in cell A1. In the cell next to it (B1) type the formula [=a1*24*60]. You must first convert the time[1:53:00] to a decimal number, Then take that number times 60 to convert it to minutes, which is 113 minutes. Or Take the 1:53:00 times 1440 which is 60 times 24. Make sure you format cell B1 to Gereran or number. I run Excel 2K I download various times from a mainframe. When the come down they look like this: 1:53:00 AM When I try a custom format to convert this to minutes I get the number 1 as a result. I use fortmat:cell:custom:m But, if I enclose the "m" in square brackets ( [m] ) then it converts the time to minutes which is what I want. I would like to know what the [ ] brackets do? Thanks |
#5
|
|||
|
|||
Time Formatting Question
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "John Calder" wrote in message ... Thanks ! This was an excellent answer to my question. Well Done ! "T. Valko" wrote: When you formatted the times as "m" Excel evaluates just "m" as the MONTH. That's why you got a displayed value of 1. Excel evaluated 1:53:00 AM as January 1, 1900 1:53:00 AM. So, m = 1 (for January). When you include the brackets [m] Excel then evaluates the "m" as minutes and the brackets keep the minutes from rolling over into hours at 60 minutes. The same thing happens and is easier to see what Excel is doing when dealing with times that span more than 24 hours. Enter 25:00 in a cell and make sure the format is h:mm. You'll see that Excel displays 1:00. The hours rolled over into days at 24 hours so Excel displays the remainder which is 1 hour (25 hours minus 24 hours). Now, change the format to [h]:mm and you'll now see that Excel displays 25:00. So, the brackets [ ] keep the unit of measure from rolling over to the next higher unit of measure. -- Biff Microsoft Excel MVP "John Calder" wrote in message ... Hi I run Excel 2K I download various times from a mainframe. When the come down they look like this: 1:53:00 AM When I try a custom format to convert this to minutes I get the number 1 as a result. I use fortmat:cell:custom:m But, if I enclose the "m" in square brackets ( [m] ) then it converts the time to minutes which is what I want. I would like to know what the [ ] brackets do? Thanks . |
Thread Tools | |
Display Modes | |
|
|