Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 - Time Formats
Hello,
I am trying to create a worksheet that will calculate how much money one has earned according to the total number of hours worked. I have tried formatting my "Time" column using the following format options: Category Type Time 13:30:55 Time 37:30:55 Custom h:mm:ss Custom [h]:mm:ss Custom h:mm:ss;@ Custom [h]:mm:ss;@ But, I am having problems obtaining the formatting I want. First, my times are all reading as "actual times", such as "05:59:59" reads as "5:59:59 AM", or"23:59:59" reads as "11:59:59 PM" instead of just "23:59:59". And second, once I go over 23:59:59, in my "time" column, such as "24:00:03", it reverts back to "12:00:03 AM". Does anyone know how I can format my "time" column so that it will not: 1.) Revert back if I exceed 23:59:59? And 2.) So that my "times" column will not read as an "actual time", but rather as the "time value" in actually enter, such as "42:55:23"? I would greatly appreciate any assistance, information, or suggestions anyone may have and I thank you for taking the time to read my question. I hope you are having a nice day. Sincerely, IJ Johnson Freelance Bookkeeper | Independent Contractor www.ij-ic.freewebsitehosting.com |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 - Time Formats
Try [hh]:mm:ss
The double hh should do what you want best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "IJ" wrote in message ... Hello, I am trying to create a worksheet that will calculate how much money one has earned according to the total number of hours worked. I have tried formatting my "Time" column using the following format options: Category Type Time 13:30:55 Time 37:30:55 Custom h:mm:ss Custom [h]:mm:ss Custom h:mm:ss;@ Custom [h]:mm:ss;@ But, I am having problems obtaining the formatting I want. First, my times are all reading as "actual times", such as "05:59:59" reads as "5:59:59 AM", or"23:59:59" reads as "11:59:59 PM" instead of just "23:59:59". And second, once I go over 23:59:59, in my "time" column, such as "24:00:03", it reverts back to "12:00:03 AM". Does anyone know how I can format my "time" column so that it will not: 1.) Revert back if I exceed 23:59:59? And 2.) So that my "times" column will not read as an "actual time", but rather as the "time value" in actually enter, such as "42:55:23"? I would greatly appreciate any assistance, information, or suggestions anyone may have and I thank you for taking the time to read my question. I hope you are having a nice day. Sincerely, IJ Johnson Freelance Bookkeeper | Independent Contractor www.ij-ic.freewebsitehosting.com |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 - Time Formats
PS: you do know that is A1 has a values in hours ([hh]:mm:ss) and B1 has
value -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme in dollars (hourly rate) then to compute earnings you need =A1*24*B1 ? Excel stores time as a fraction of a day; the 24 converts it to actual hours. best wishes "IJ" wrote in message ... Hello, I am trying to create a worksheet that will calculate how much money one has earned according to the total number of hours worked. I have tried formatting my "Time" column using the following format options: Category Type Time 13:30:55 Time 37:30:55 Custom h:mm:ss Custom [h]:mm:ss Custom h:mm:ss;@ Custom [h]:mm:ss;@ But, I am having problems obtaining the formatting I want. First, my times are all reading as "actual times", such as "05:59:59" reads as "5:59:59 AM", or"23:59:59" reads as "11:59:59 PM" instead of just "23:59:59". And second, once I go over 23:59:59, in my "time" column, such as "24:00:03", it reverts back to "12:00:03 AM". Does anyone know how I can format my "time" column so that it will not: 1.) Revert back if I exceed 23:59:59? And 2.) So that my "times" column will not read as an "actual time", but rather as the "time value" in actually enter, such as "42:55:23"? I would greatly appreciate any assistance, information, or suggestions anyone may have and I thank you for taking the time to read my question. I hope you are having a nice day. Sincerely, IJ Johnson Freelance Bookkeeper | Independent Contractor www.ij-ic.freewebsitehosting.com |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 - Time Formats
hi
i have the same problems and per chip person, there is no format that will do what you want. the format [dd]:hh:mm:ss will not roll over the day as the hour format [hh]:mm:ss rolls over the hour. but he did profide me with a workaround. i had to create a second column and use this formula =INT(AS3)&":"&TEXT(AS3-INT(AS3),"hh:mm:ss") adjust cell references to fit your data. in my worksheet, the sheet is set up to measure a consumption process that goes over 30 days now and then. the times are undated with a macro (manually becasue we can't connect excel to the process) and the column that holds the real time is hidden. the column with the real time doesnt' roll over but the column with the formula does. maybe not the best solution but so far, it's fooled our engeneers. and the time the formula displays is as accurate at the real time. not sure if it will help but so far as i know, it's the only workaround going right now. Regards FSt1 "IJ" wrote: Hello, I am trying to create a worksheet that will calculate how much money one has earned according to the total number of hours worked. I have tried formatting my "Time" column using the following format options: Category Type Time 13:30:55 Time 37:30:55 Custom h:mm:ss Custom [h]:mm:ss Custom h:mm:ss;@ Custom [h]:mm:ss;@ But, I am having problems obtaining the formatting I want. First, my times are all reading as "actual times", such as "05:59:59" reads as "5:59:59 AM", or"23:59:59" reads as "11:59:59 PM" instead of just "23:59:59". And second, once I go over 23:59:59, in my "time" column, such as "24:00:03", it reverts back to "12:00:03 AM". Does anyone know how I can format my "time" column so that it will not: 1.) Revert back if I exceed 23:59:59? And 2.) So that my "times" column will not read as an "actual time", but rather as the "time value" in actually enter, such as "42:55:23"? I would greatly appreciate any assistance, information, or suggestions anyone may have and I thank you for taking the time to read my question. I hope you are having a nice day. Sincerely, IJ Johnson Freelance Bookkeeper | Independent Contractor www.ij-ic.freewebsitehosting.com |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 - Time Formats
1. Use a format of [hh]:mm:ss. The square brackets tell Excel not to roll
over at 24 hours. 2. Same answer as 1. This will format the cell. If you are looking at the formula bar, times will be displayed as per your Windows Regional Settings. Regards, Fred "IJ" wrote in message ... Hello, I am trying to create a worksheet that will calculate how much money one has earned according to the total number of hours worked. I have tried formatting my "Time" column using the following format options: Category Type Time 13:30:55 Time 37:30:55 Custom h:mm:ss Custom [h]:mm:ss Custom h:mm:ss;@ Custom [h]:mm:ss;@ But, I am having problems obtaining the formatting I want. First, my times are all reading as "actual times", such as "05:59:59" reads as "5:59:59 AM", or"23:59:59" reads as "11:59:59 PM" instead of just "23:59:59". And second, once I go over 23:59:59, in my "time" column, such as "24:00:03", it reverts back to "12:00:03 AM". Does anyone know how I can format my "time" column so that it will not: 1.) Revert back if I exceed 23:59:59? And 2.) So that my "times" column will not read as an "actual time", but rather as the "time value" in actually enter, such as "42:55:23"? I would greatly appreciate any assistance, information, or suggestions anyone may have and I thank you for taking the time to read my question. I hope you are having a nice day. Sincerely, IJ Johnson Freelance Bookkeeper | Independent Contractor www.ij-ic.freewebsitehosting.com |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 - Time Formats
"IJ" wrote:
I have tried formatting my "Time" column using the following format options: [....] Custom [h]:mm:ss Something seems off. That Custom format should have worked for you, unless you want the hours to always have at least 2 digits (possible leading zero), in which case you want [hh]:mm:ss. "05:59:59" reads as "5:59:59 AM", or"23:59:59" reads as "11:59:59 PM" instead of just "23:59:59". And second, once I go over 23:59:59, in my "time" column, such as "24:00:03", it reverts back to "12:00:03 AM". If you are looking in the Formula Bar, you will always see AM/PM times and sometimes even dates like 1/1/1900. And yes, that makes it difficult to edit some constants. But with the Custom format [h]:mm:ss, you should see 24:00:03 in the cell. Are you simply looking in the wrong place (Formula Bar)? ----- original message ----- "IJ" wrote: Hello, I am trying to create a worksheet that will calculate how much money one has earned according to the total number of hours worked. I have tried formatting my "Time" column using the following format options: Category Type Time 13:30:55 Time 37:30:55 Custom h:mm:ss Custom [h]:mm:ss Custom h:mm:ss;@ Custom [h]:mm:ss;@ But, I am having problems obtaining the formatting I want. First, my times are all reading as "actual times", such as "05:59:59" reads as "5:59:59 AM", or"23:59:59" reads as "11:59:59 PM" instead of just "23:59:59". And second, once I go over 23:59:59, in my "time" column, such as "24:00:03", it reverts back to "12:00:03 AM". Does anyone know how I can format my "time" column so that it will not: 1.) Revert back if I exceed 23:59:59? And 2.) So that my "times" column will not read as an "actual time", but rather as the "time value" in actually enter, such as "42:55:23"? I would greatly appreciate any assistance, information, or suggestions anyone may have and I thank you for taking the time to read my question. I hope you are having a nice day. Sincerely, IJ Johnson Freelance Bookkeeper | Independent Contractor www.ij-ic.freewebsitehosting.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 and Time Formats | Excel Discussion (Misc queries) | |||
Saving in 2007 and 2003 formats at the same time | Excel Discussion (Misc queries) | |||
TIME FORMATS IN EXCEL | Excel Discussion (Misc queries) | |||
Time formats in Excel | Excel Discussion (Misc queries) | |||
Time formats in Excel 2003 | Excel Discussion (Misc queries) |