• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Format In General from Time

A

Abhijeet

Active Member
I have data where Time format in column A i want all that data in general format please tell me any format cell data change to Number Format with same value reflect in that cell.Is it possible with Macro or formula please help for this
 

Attachments

  • Format cell.xlsx
    10.1 KB · Views: 8
With custom number formats you will always gets the minutes if they are chosen as 00 is a valid minute
You can use h.mm as a custom number format but it will display 3 hrs as 3.00
 
If Hui's suggestion is not adequate, you will need to actually modify the data. You could use this formula:
=HOUR(A2)+MINUTE(A2)/100
Format cell as general, as it will try to default to time, but then you can copy down and get desired result.
 
Luke M formula not work i want from time format cell data change to Number format but do not change the value
 
Abhijeet said:
Luke M formula not work i want from time format cell data change to Number format but do not change the value
Click to expand...
That's why I said "If Hui's suggestion is not adequate..." :eek:

You are attempting to display a time value in an unconventional manner. We can either format the time using one of the options available and get close to desired format, or we can manipulate the data into the format. Those are the only options.
 
Hi Abhijeet ,

The issue is simple ; you want the value which is in Time format to be displayed as a value in decimal format.

Time values in Excel are decimal numbers , but their range is from 0 through 1 ; thus a Time value such as 03:00 is actually a decimal value of 0.125 , while a Time value of 16:00 is actually a decimal value of 0.666667.

To actually display a value of 0.666667 as 16.00 is not possible using just cell formats ; you have two options :

1. Use a formula to convert the value from 0.666667 to 16.00 ; Luke has already posted a formula which does exactly this ; since it is a formula , it cannot change the value within the same cell ; you need to enter this formula in another cell.

2. Use a macro , which does the conversion ; since it is a macro , it can replace the existing Time values with the desired values.

If you want a macro , do you want one which does the conversion and replacement as you enter a Time value , or do you want one which operates on a range of already entered Time values , does the conversion on the entire range , and repopulates all the cells in the range with their corresponding hh.mm values.

The first one will be a Worksheet_Change event procedure , while the second will be a procedure which you will have to run each time you want the conversion done.

Please clarify.

Narayan
 
Hi Narayan

I have data in cell Time Format like this 12:30:25 but i want 12.30 in General Format i don't want convert the cell value
 
Abhijeet said:
I have data in cell Time Format like this 12:30:25 but i want 12.30 in General Format i don't want convert the cell value
Click to expand...
You. Can't. Do. That.

If you don't mind the extra 0, which is different than what you originally stated, Hui's custom format will work. However, you can't expect "General" format to make changes to how a cell displays a value. That would be contradictory to it's definition!
 
Hi Luke i get solution =text(a1,"hh.mm") then convert that cell in to general format
 
Last edited:
So, you found a formula solution that forces a formatting change, just so you can then format the cell with formula to be General?? o_O:confused: I have no idea what your end goal is in all of this, but it sounds very confusing.
 
Abhijeet,
You can not perform any calculation in this unless you add 0 to your result or multiply your result by 1.You may find =(TEXT(A1,"hh.mm"))+0 bit more useful.

With Regards
Rudra
 
Thank
rumshar said:
Abhijeet,
You can not perform any calculation in this unless you add 0 to your result or multiply your result by 1.You may find =(TEXT(A1,"hh.mm"))+0 bit more useful.

With Regards
Rudra
Click to expand...
Thanks for tell the changes in formula
 
You must log in or register to reply here.
Back
Top