Free Microsoft Excel 2013 Quick Reference

Converting time in [H]:mm to [H]:decimal minute for more times more than 24 hours

Hi there,

I'm having problem trying to convert the [H]:mm time to the [H]:decimal minute for the spreadsheet that I'm attaching here(problem in blue column). The time cumulative needs to be in decimal so that the graph (diffusion vs. time) would look right. I tried the 24*A1 solution but since the cell has other arithmetic function it messes up the numbers. Could someone please help.

regards,
ZaHiR


Post your answer or comment

comments powered by Disqus
Good morning,

Is there a formula or easy way to automatically convert a custom h:mm format
to a decimal format?

Situation:
I am working on putting together an Excel Template for my boss for our
departmental time sheets. I currently have these basic categories setup
(worded differently but will use descriptive terms in this example).

Day AM TIME IN, AM TIME OUT, PM TIME IN, PM TIME OUT, Total (h:mm
format), and then a Total column (Decimal format)

Currently I have the AM, PM cells and Total h:mm cells formatted as "custom"
and set to "h:mm". And the Total Decimal Column format is set to "Number" and
to 2 decimal places. Reason for 2 total columns is to sum the h:mm values to
get a total value, and payroll needs the values in a decimal format (e.g.
8.25 hours).
Suggestions would be greatly appreciated.

Cordially,

Good morning,

Is there a formula or easy way to automatically convert a custom h:mm format
to a decimal format?

Situation:
I am working on putting together an Excel Template for my boss for our
departmental time sheets. I currently have these basic categories setup
(worded differently but will use descriptive terms in this example).

Day AM TIME IN, AM TIME OUT, PM TIME IN, PM TIME OUT, Total (h:mm
format), and then a Total column (Decimal format)

Currently I have the AM, PM cells and Total h:mm cells formatted as "custom"
and set to "h:mm". And the Total Decimal Column format is set to "Number" and
to 2 decimal places. Reason for 2 total columns is to sum the h:mm values to
get a total value, and payroll needs the values in a decimal format (e.g.
8.25 hours).
Suggestions would be greatly appreciated.

Cordially,

Hello,

I use Excel 2007,
I want to substract time values in [h]:mm format,
for example I want to substract 35:00 from 80:00 and I want to see 45:00 as a result,
Also I want to substract 70:00 from 30:00 and I want to get -40:00 as a result,
but excel does not display negatve results, it displays "#######" instead. How can i fix it?
Thanks,
Best Regards,

Hi Folks, I'm seeking code to convert the following futures data quoted in fractions (after the apostrophe) to decimal (quotes for treasury notes and bonds)?

Data in fraction-format
125'08.5
125'14.0
124'17.5

....needs to be converted to decimal-format:
125.265625
125.4375
124.546875

Where '08.5 = 8.5/32 = 0.265625
'14.0 = 14.0/32 = 0.4375
'17.5 = 17.5/32 = 0.546875
ie number after apostrophe is the numerator-value in the format xx.x, denominator value=32):

Thanks in advance for your help and the great forum.

Hi,

In Excel 2007, I am trying to make calculation on cells containing time in [h]:mm format. However, it doesn't work when the result is negative, and get only ####### instead! Is there any way to obtain the proper negative result?

I have a group of numbers that I need to convert from mm:ss formatting to decimal minute format such as 10 minutes 30 seconds (10:30 to 10.50) or (08:22 to 08.366). I have read the threads that show how to convert decimal minutes to seconds but I wasn't able to convert around to the solution I need.

Thanks for your valuable time.

Here lies the problem. I'm cheap, I want to be paid for every minute that I
work, I do the time cards for work and I'm using Excel to perform the
functions. I have no problem with adding and subtracting the times in single
cells or totalling all of the time. If I come up with a person having 47
hours and 13 minutes how do I get Excel to convert 47:13 to 47.22 so that I
can then multiply the 40 by hourly rate and the 7.22 by 1.5 the hourly rate ?
--
Thank You, God Speed, and Semper Fidelis

I have a cell that has a start time in the format of 12:43, and a cell that has an ending time in the same format. I used the =TEXT(B3-A3,"h:mm") to find what the elapsed time between the start and end times. I now have to find the total of these times. With this formula it will not come up with the answer. Can any one help me convert these so that I can total them?

How do I convert more than 24 hours time to decimal?
--
Many thanks for your help in advance. Have a wonderful day!
Zsolt

Hello all:

I'm new to the board, but can see this being a valuable resource for me in the future. I am trying to do two things:

1. Convert date and time in this format from GMT -> EST for these dates / times.

Current format is: YYYY-MM-DD-HH-MM-SS-MSMSMSMSMSMS
2010-03-18-03.55.30.477460
2010-03-18-03.55.30.648572

2. After converting, I need to find out the difference between the two dates / times.

Would appreciate any help anyone can provide.

Thank you in advance!

Hi,
I'm trying to write a macro where the current time needs to b compared with a parameter entered by user.
The user enters the time in hh:mm:ss.ms format.

This is the code im trying to use

	VB:
	
 ((Format(Now, "hh:mm:ss.ms") = startTime)) 
Workbooks("OrderTrigger").Worksheets("Sheet1").Range("H" & (length + j)).Value = 0 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
where startTime is the user param for eg:09:15:01.001

When this condition is true nothing seems to be happening...

Please let me know if there is some other way to do it...

Thanks in advance for any help!

Hello

I need your assistance:

I have a Date in dd/mm/yyyy & time in h:mm:s format in 2 cells. I want to calculate the lapsed time. I normall use =now()-A2 for any time lapse. But when it is associated with a date, say for eg.

A1 : 26/02/2007
A2 : 16:08:35
Lapsed time in A3 as per my formula (=now()-A2) is not considering the lapsed days.

My requirements are:

The lapsed time should be calculated keeping in consideration of A1 date and the current date. The result need to be in "minutes"

From the lapsed time, I should be able to get a data in another cell with the following conditions

=lapsed time-(55 minutes)

So this should help me in finding how we are approaching to 55 minutes and there should be a conditional format in this cell that if the resulting time in minutes crosses 55 minutes, it should change its colour.....

Pl help me in this

Khanna

Hello, I need to add up the times (in h:mm format) of a row of cells. The
sheet I have been given has its base cell (C7) that has been named as Mhours.
This is defined as adding the values in row seven cells from D to AA. The
values for these cells are copied from another spreadsheet where the hours
are displayed in the h:mm format.
My problem is that to calculate I have to drop the : and replace it with a .
instead. The sum of D7:AA7 is not a true h:mm value and, therefore, makes
the final value incorrect.
What formula should be in the definition of the named cell or is there an
alternative formula without naming the cell (C7)? I have tried the
'=TEXT(D7:AA7,"h:mm")' formula, but this does not work; an error seems to
indicate a conflict with the two :'s.
Thanks.

I am a first-time poster, and am pleased to be part of this community. I will try to be a good "citizen".

I think I may have a new issue regarding the formatting of times greater than 24 hours using VBA. I have researched many forums regarding this problem without success.

On this Ozgrid forum on February 8, 2008, Andy Pope posted this code:

	VB:
	
 WorksheetFunction 
    frmMainMenu.Label3.caption  = .Text(. Sum(Worksheets("open"). Range("g2:g60000"")), "[h]:mm:ss") 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This seems to work fine. However, I want to show only hours and minutes and thus modified Andy's code as follows:


	VB:
	
 TimeFormatTester() 
     
    Dim TimeTestData As Single 
    Dim i As Integer 
     
    For i = 1 To 10 
        Cells(i, 1).Clear 'Clear all previous contents and formats
        TimeTestData = 0.5 + i / 10 'Start at noon and increment by 2.4 hours
         
         'Modified Andy Pope's suggestion posted on Ozgrid on February 8, 2008
        Cells(i, 1) = WorksheetFunction.Text(TimeTestData, "[h]:mm") 
         
    Next i 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The output is fine (i.e. hours and minutes) until my variable TimeTestData is >=1 at which point, the format reverts - for some reason - to hh:mm:ss.

I'd love to know what's going on, and find a fix which leaves the cell contents as a number.

Thanks.

How do I get a straight conversion from h:mm to h:tenths? As soon as it hits 23:59, the clock start over in the tenths column. ie...39:27 should read 39.4. I'm currently using this formula: =(T10-INT(T10))*24

3:28 3.5
8:50 8.8
9:26 9.4
10:42 10.7
12:57 12.9
14:12 14.2
39:27 15.4
41:42 17.7
59:57 11.9

Thanks in advance for the help!

I've been reading through all threads and trying out the various formulas but I'm still having problems converting. This is what I have per week (the times vary).

Start Finish Break Hours
10:00 19:00 00:30 8:30:00
07:00 19:00 00:30 11:30:00
07:00 19:00 00:30 11:30:00
07:00 19:00 00:30 11:30:00
07:00 17:00 00:30 9:30:00
Total 52:30:00
Basic 35:00:00
OverT 17:30:00

I now need to convert the Basic and OverT to decimal and multiply them by the rate of pay. for instance

Running total
Basic 175.00 x 9.00 = £1575.00
OverT 87.50 x 12.00 = £1050.00
Gross = £2650.00

For some reason 35:00:00 always comes out as 11.00.

I'm probably being a thicko and missing something simple, but I'm an electrician not a computer wizz.

Hello,

I am having trouble in making a Sub procedure for an excel file, and I was wondering if someone could help me write the code. Here is the problem:

I have an excel sheet that only has dates in Column A. In Column B, I have times in h:mm format corresponding to the date. An example section:

A B C D
2/13/2006 22:01 11762 CFM
2/13/2006 22:16 10843 CFM
2/13/2006 22:31 11033 CFM
2/13/2006 22:46 10971 CFM
2/13/2006 23:01 11015 CFM
2/13/2006 23:16 11742 CFM
2/13/2006 23:31 11057 CFM
2/13/2006 23:46 11045 CFM
2/14/2006 0:01 11617 CFM
2/14/2006 0:16 6771 CFM
2/14/2006 0:31 7506 CFM
2/14/2006 0:46 7732 CFM
2/14/2006 1:01 7553 CFM
2/14/2006 1:16 10849 CFM
2/14/2006 1:31 11045 CFM
2/14/2006 1:46 10791 CFM
2/14/2006 2:01 10881 CFM What I need to do is have only one row in the same date for the specific hour period. i.e, For 2/13/2006, there would be only one row for the hour 23:mm, deleting the other 23:mm for the same date. The criteria is the minutes closest to 00. i.e, if there is 12:05 and 12:23, it would pick 12:05 and delete the other(s).

I could do this manually but it would take forever since there are 17 sheets with around 7000+ rows .

I would really appreciate the help for this! Btw, this is for a college research project relating to airconditioning.

how do u find difference between 2 times in yyyy.mm.dd hh:mm format in excel 07, i've spent hrs trying to reformat the cells, tried every concoction and all i keep getting is #VALUE!

col A
2010.12.27 06:27
2010.12.27 08:45
2010.12.27 09:37
2010.12.27 10:33

col B
2010.12.27 08:10
2010.12.27 09:15
2010.12.27 10:29
2010.12.27 13:38

and i'm trying to subtract A from B but getting nowhere, i'm trying to get the answer in dd-hh:mm
i'm looking for the time elapsed.

thanks for any help anyone can give

Hello,

can anyone help?

I am trying to show times in HH:MM:SS however if the time in 00:02:57 excel will not display the first to zero's. I need these to be displayed.

Is there i way i can do this? The current formatting on the cell is hh:mm:ss.

Thanks

Clare

Hi,

I have a column that adds time which has been formatted to HH:MM in custom
format. Just noticed that once the result reaches 24 hours then it restarts
at zero. Not exactly what I had in mind! Could anyone suggest a workaround
for this please.

Whilst I'm here... I wanted to do a data validation for this range to ensure
that users enter times as HH:MM but couldn't figure out how to do it Time did
not seem to be the right option as users will be adding discreet amounts of
time rather than entering a specific time. Couldn't figure how to use custom;
tried HH:MM but think I'm barking up the wrong tree.
Any help would be very much appreciated.

TIA
--
Smudge

Trying to do a sum( of time spent at telemarketing campaigns.
In order to calulate a true sum ,for several hundered campaigns, I'm entering
the total time spent in h:mm:ss and multiplying for the cost(€) per hour.
This works until the amount of time spent has 5 digits in the hours (ie, 12926:44:00).
As soon as I have a time like that I get an #value! error in the cell where the multiplication is done.
I have noticed that in the cells where the time has less than 5 digits in the hours,
the format is a date/time even though that's not how I set it up or how I'm entering the data
(ie, I enter 7715:46:00, but 11/16/1900 11:46:00 AM appears in the formula bar).
Help. I need this for a report due ASAP and been unable to find an answer anywhere.

Hello,
I am having trouble with converting h:mm to seconds. I copy and data from a report to excel and then I have a formula which helps me adjust a reps productivity. Here is my formula SUM(C7/100)+(D7/B7). I would like to have B7 converted to seconds to allow my formula to work. Any help would be appreciated.

Here is my attached worksheet

I want to split the date & time in one cell to be 2 colums; Date and another
colum is Time. i.e. 4/11/2006 14.30
How should I do?

Hi,

I need to create a spreadsheet that can convert time into hours, the inbuilt
functions only return times within a 24hour period which is completely
useless to me. I need to give a start date & time, i.e

24 August 2004 09:15

and an end date/time

24 August 2005 11:23

and have it return the total running time in a variety of formats, including
plain simple total hours (i.e. one years worth plus a couple).

Any ideas how to do this. I want to be able to keep a running total, as well
but I can probably figure it out once I can get the calculation for the
total (greater than 24 hours) hours working.

Regards

J


No luck finding an answer? You could always try Google.