Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

VBA to convert 12 hour to 24 hour time

I've got a large spreadsheet that displays time in " 0130P" format and need to convert that to military time so that it reads "13:30". Is there and easy way or programming function to call this?

Thanks!!!


Post your answer or comment

comments powered by Disqus
I use Excel to evaluate the results from different competitions. For exemple
I use an start hour (2005-06-12 09.10.00) and the finish time (2005-06-17
17.39.14) from an offshore sailing race to get the hours sailed. Then I can
convert the hours (128.29.14) to decimal hour by using the =HOUR() etc to
extract the hour, minuts etc. With the decimal hour I can calculate the speed
in km/h or in this case knots.

But my question is how I can go the other way to convert decimal hour (1,5)
to time format (01.30.00)?

Hi - Does Excel have a function for converting 12-hour time entries to
24-hour time entires? I have staff keeping track of "wait time" for an event
and I need the data in 24 hour format for equations. This doesn't always
happen.

Also, they will occasionally use ctrl-shift-; to enter the time, but I think
it enters in 12-hour format. Is there a shortcut for 24-hour format?

Thanks,
Nathan

I have a ton of time in the above format (hh:mm.*) that needs to be converted to a 12 hour format (2:15 PM). I'm not wonderful with formulas, but if it's explained "simplistic" enough, I can probably get it. Please help - I've googled this to death every possible way. I can go in an manually change 19:37.5 to 7:37 pm - but it will take me FOREVER!! Thanks in advance.

I have a column that is sent to us with values like the following: 1.75, 2.10, etc. I need to convert that into 1 hours 45 minutes (1:45) or 2 hours 30 minutes, 2:30. I followed this link,

http://www.ozgrid.com/forum/showthre...ours%2Fminutes

And understand the formula but have no clue on how to implement it. It shows a hardcoded value of 225 and I want it to use a cell reference. Any thoughts on this? TIA.

I have a column where the amount of time is given as "01h 30m", which means one hour and thirty minutes. I want to be able to convert this to decimal format where it would remove the letters and divide the minutes by 60 so the decimal format for the above would be "1.5". This needs to be done for any type of time, such as "02h 45m", "01h 20m", etc. Thanks!

Being in the business of producing railway timetables, I'd like Excel to be
able to format times in a 12-hour format *without* having am/pm after each
time. In timetables, the am/pm is normally shown at the top of the column of
times, not against each time.

(I'm currently using Excel 2003)

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

How can I convert total time (HH:MM) into a decimal (HH.MM)
Where 30 minutes (xx:30) equals .50.
So far I have the formula
=(INT(VALUE(O16))*24)+(HOUR(VALUE(O16)))+((MINUTE( VALUE(O16))/100))
but still need to convert minutes to decimal

Hi,

I have a problem using Excel to recognise 12-hour time data entries. If I enter 3:00 p in a cell, Excel will treat it as text.

In order to get Excel to recognise the correct format, I must use the 24-hour format, i.e I must enter 15:00 so that Excel knows it is 3:00 PM.

I have tried using both cell format General and Time. Still, it does not change the situation. I have also check the Regional and Language settings.

Does anyone encounter such issue with Excel?

Thanks and regards.

Being in the business of producing railway timetables, I'd like Excel to be
able to format times in a 12-hour format *without* having am/pm after each
time. In timetables, the am/pm is normally shown at the top of the column of
times, not against each time.

(I'm currently using Excel 2003)

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

At my company we have a program that we consistantly use to export data to excel. Unfortunatley the program codes business units with a combination of its own code and our internal code making it very time consuming to sort and decipher. Is there anyway to use VBA to automatically convert these names to our standard code. I was thinking maybe a v-lookup but i am not sure how to use a v lookup with more than 3 columns of data to sort through. I would greatly appreciate any bright idea

thanks

Hi everybody,

Would like your help to advise on ow to set a formula on the following
Example : for 4/13/2006 11:22, how to set a formula to convert the US
mountain time to Asia/Singapore time?

US mountain time Convert to Asia/Singapore time
=========== ====================
4/13/2006 11:22 Please advise me the fourmula
4/13/2006 11:22
4/13/2006 11:22
4/13/2006 11:21
4/13/2006 11:20
4/13/2006 11:21
4/13/2006 11:20
4/13/2006 12:20
4/17/2006 3:10
4/18/2006 3:59
4/19/2006 2:32
4/18/2006 18:00
4/18/2006 2:59
4/18/2006 2:59
4/19/2006 2:34
4/19/2006 11:32

Your help is really much appreciated.

Thank you.

Okay, so I am creating an electronic timesheet (see below). The issue I am having is the first "In/Out" rows are deemed "1st Shift", the second "In/Out" rows are "2nd Shift", and the 3rd "In/Out" rows are "3rd Shift". When these are hand written, third shift (11pm to 7am) puts in 2300 - 2400 in the "3rd Shift" row on the day it is relevant to, and then puts 0000 - 0700 in the "1st Shift" row the following day. When a person looks at this handwritten timesheet, it is understood that 0000 - 0700 is 3rd shift from the night before, even though it is put in the 1st shift row. Clear as mud?

So, wouldn't it be nice if Excel could simply subtract 0700 on day 2 from 2300 the night before and have it = 8 hours. Unfortunately (as far as I can tell) it is not possible. So, I thought of changing the times (as seen in column W) to text, and just making them labels that equal the values located in column Y. This way I could have someone work 11pm to 11am (2300 - 1100) and be able to put in the 3rd shift row "2300 for "In" and 1100 for "Out" (all in the 3rd Row In/Out on the night where the shift started) and have it understand that 2300 = 23.00 and 1100 = 35.00; therefore 35 - 23 = 12 Hours for 3rd Shift???

I hope my description and screenshot are sufficient to get my question answered; please let me know if any futher clarification is needed. Thank you in advance for any assistance anyone can provide! The link below will take you to a screenshot of what I describe.

P.S. I thought about using If/then statements (for example IF D19 = W10, then Y10), but I am limited to like 30 or 60 of these, and I would need 144 of them to cover values for everything between 0000 on day 1 and 1100 on day 2 based on every quarter hour (total of 12 hours to include the .25, .50, and .75 for 15 past the hour, 30 past the hour and 45 past the hour...36 Hours x 4 = 144)

http://vanwyk.net/jon/spreadsheet01.jpg

Greetings again,

I am attempting to convert a spreadsheet of times (listed in the format 06:15:39.62, where 06 is the hour, 15 is the minutes, 39 is the seconds, and .62 is in truncated miliseconds) into fractions of hours (so, 6.25 [NOT 6:25!]). I've so far been doing it manually for each value, which is quite tedious (doing basic division of seconds and minutes into hours, to find the fraction) but I'd like a single formula which I can then apply to the whole spreadsheet.

Unfortunately, I am at a total loss as to how to do this. Any help is appreciated.

Hi,

I pull the data in the attached spreadsheet from a SharePoint query. The data is pulled over into Excel 2007 in the form of a table. I would like to convert the data in columns C through I from text to numeric so that I can use the AVERAGE forumula to calculate averages for each row. I would like to be able to do this through VBA as I will also automate adding and population of the averaged column. The only problem I see is that this data is dynamic. Each month I clear the spreadsheet and run the query to grab the latest data so I need to be able to select the data from a dynamic range and convert it to numbers. Any ideas on how I can accomplish this with VBA?

Thanks in advance!

Hi,

Can someone help me, what i need is some vba code to convert a price with a fraction in it to a decimal figure.

Eg in cell A1 you have 1.03 1/2
B1 56.05 47/64
ect
i need to convert to 1.035

thanks
Mark

I need to insert an Excel worksheet in the PACS (Picture Archiving and
Communications System) in our medical imaging department. Our
PACS only accept JPEG or TIFF or DICOM format. Currently, we convert the
Excel sheet to PDF and then JPEG and then insert it in the PACS.
Is there a way (macro? VBA?) to program a cell (lets call it "Save as a
Picture") so that when I click on that cell, the worksheet get saved as jpeg
or tiff or dicom format?
Thank you for any comments or help in advance.
Dori

I need to insert an Excel worksheet in the PACS (Picture Archiving and
Communications System) in our medical imaging department. Our
PACS only accept JPEG or TIFF or DICOM format. Currently, we convert the
Excel sheet to PDF and then JPEG and then insert it in the PACS.
Is there a way (macro? VBA?) to program a cell (lets call it "Save as a
Picture") so that when I click on that cell, the worksheet get saved as jpeg
or tiff or dicom format?
Thank you for any comments or help in advance.
Dori

I have some VBA code that is importing a file from another source. The vendor who created the source file did not take into account how excel treats the 2-digit year. As a result, they are sending me dates that occurs in the year 19xx when it needs to be 20xx. I need to convert any date with a year from 1930-1999 to year 2030-2999. The dates are in column E and I want convert to values. Any assistance is appreciated. Thanks

    With wsAnalysis
        EndRow = .Range("E" & .Rows.Count).End(xlUp).Row
        For i = 2 To EndRow
            Cells(i, 5).Value = 
        Next i
    End With


Hi
will u plz tell me how to convert a no into time
e.g. I want to calculate no of hours I worked in office for hat i entered in time & out time say 10.05 & 18.02 when i subtract out time from in time to calculate no of hours i worked it shows me 8.97 but idealy it should be 9hrs & 37 min so plz help me to get me the required result.

I would like to convert fractional hours into hours and minutes. For example,
1.47 hours is 1 hour 28 minutes or 01:28:00.

Hi,

Can anyone help me getting formula to convert Pacific Standard Time (PST) into Indian Standard Time (IST). The result should show time in AM/PM

Thanks in advance.

I am writing a macro that selects an absolute formula from one worksheet and pastes it to another cell in another worksheet. The formula that is pasted is as follows
=IF(AND(Input!$C$3="",Input!$D$3=""),"",IF($F$3="cash","cash",IF(Input!$C$3>0,Input!$C$2,Input!$D$2)))

I now need to write code to change the formula to this
=IF(AND(Input!C3="",Input!D3=""),"",IF(F3="cash","cash",IF(Input!C3>0,Input!$C$2,Input!$D$2)))

Basically I need to convert characters C3, D3, F3 to RELATIVE while leaving characters $c$2, and $D$2 remain as ABSOLUTE. I know how to convert the entire formula to relative, but need help in coverting only a portion of the formula,

Suggestions are very welcomed

Marty

Hello,

what is the best way in VBA to convert everything to values in a sheet whilst retaining the format of the cells?

thanks a lot
Andy

can any one help me to covert 12/10/1970 to years/months/days


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