Tags: cell, convert, decimal, excel, format, microsoft, msdn, particular, second, seconds, software, transaction

How do I convert hours, minutes, and seconds into decimal format?

On Microsoft » Microsoft Excel

3,150 words with 2 Comments; publish: Fri, 23 May 2008 18:20:00 GMT; (306375.00, « »)

In cell A1 I have the day, month, year, hour, minute, and second a particular

transaction took place. In cell A2 I have the day, month, year, hour, minute,

and second the next transaction takes place. The Start and End dates and

times are used to determine the total number of hours, minutes and seconds

for one transaction. I have the total number of hours, minutes, and seconds

for a given transaction in column A3. If the total time in column A3 is

greater than 12:00:00, I want to return the value of 1 Business Day in column

A4. If the total is less than 12:00:00 I want to return the total hours,

minutes, and seconds in decimal format in column A4. Is this scientifically

possible?

All Comments

Leave a comment...

  • 2 Comments
    • Yes, it's possible. It depends what you mean by "hours, minutes, and seconds in

      decimal format". Do you want decimal hours, minutes, seconds, or something else?

      Time is stored in Excel as a fraction of a day, so 12:00:00 is 0.5 days. If you

      multiply a time by 24, you will get decimal hours. So, the following should

      work:

      =if(a3>0.5,1,a3*24)

      Format as number, or general.

      --

      Regards,

      Fred

      "Programmer Wannabe" <Programmer Wannabe.excel.questionfor.info.discussions.microsoft.com> wrote in

      message news:AC915ABB-109D-4E53-BE78-86193B77CD2A.excel.questionfor.info.microsoft.com...

      > In cell A1 I have the day, month, year, hour, minute, and second a particular

      > transaction took place. In cell A2 I have the day, month, year, hour, minute,

      > and second the next transaction takes place. The Start and End dates and

      > times are used to determine the total number of hours, minutes and seconds

      > for one transaction. I have the total number of hours, minutes, and seconds

      > for a given transaction in column A3. If the total time in column A3 is

      > greater than 12:00:00, I want to return the value of 1 Business Day in column

      > A4. If the total is less than 12:00:00 I want to return the total hours,

      > minutes, and seconds in decimal format in column A4. Is this scientifically

      > possible?

      #1; Fri, 23 May 2008 18:21:00 GMT
    • Try this in A4:

      =IF(ISNUMBER(A3),IF(A3>0.5,"1 Business Day",A3*24),"")

      Biff

      "Programmer Wannabe" <Programmer Wannabe.excel.questionfor.info.discussions.microsoft.com> wrote in

      message news:AC915ABB-109D-4E53-BE78-86193B77CD2A.excel.questionfor.info.microsoft.com...

      > In cell A1 I have the day, month, year, hour, minute, and second a

      > particular

      > transaction took place. In cell A2 I have the day, month, year, hour,

      > minute,

      > and second the next transaction takes place. The Start and End dates and

      > times are used to determine the total number of hours, minutes and seconds

      > for one transaction. I have the total number of hours, minutes, and

      > seconds

      > for a given transaction in column A3. If the total time in column A3 is

      > greater than 12:00:00, I want to return the value of 1 Business Day in

      > column

      > A4. If the total is less than 12:00:00 I want to return the total hours,

      > minutes, and seconds in decimal format in column A4. Is this

      > scientifically

      > possible?

      #2; Fri, 23 May 2008 18:22:00 GMT