convert numbers to Date and numbers to Time
Hi,
I use the same spreadsheet daily and would like certain columns to be permanently formatted
for Date and Time so that any new data will automatically be converted to Date and Time formatsnumberconvertdataetime.xlsx.
Please refer to spreadsheet
Many Thanks
Ian
I use the same spreadsheet daily and would like certain columns to be permanently formatted
for Date and Time so that any new data will automatically be converted to Date and Time formatsnumberconvertdataetime.xlsx.
Please refer to spreadsheet
Many Thanks
Ian
Does the value in D11 & F11 is in the same format always - yyyymmdd & hh.mm?
ASKER
Tom, The format of cells D11 and F11 changes when a number format is pasted. I need the cells format to remain.
Sai The data being pasted to those cells are in number format yes yyyymmdd and number 3.15 or 3 which represents
3:15 and 3:00 Time format
so the new data example 20190921 should convert to 21/9/19 Date format and the 3.15 convert to 3:15 Time format.
So when new number data is pasted it will be converted to the format of the cells. The cells format should never change.
Sai The data being pasted to those cells are in number format yes yyyymmdd and number 3.15 or 3 which represents
3:15 and 3:00 Time format
so the new data example 20190921 should convert to 21/9/19 Date format and the 3.15 convert to 3:15 Time format.
So when new number data is pasted it will be converted to the format of the cells. The cells format should never change.
Not sure you can create that format directly in the cells D11 and F11. You can put formulas with custom formatting that refers to entries in (my example) the cells above D11 and F11.
Additionally I am still not sure from your example how you would know 3:15 was AM or PM, or if you care.
EE1.xlsx
Additionally I am still not sure from your example how you would know 3:15 was AM or PM, or if you care.
EE1.xlsx
ASKER
I have found a formula in my old workbook that works
TIME(INT($A1),100*($A1-INT($A1)))
It converts numbers to time format
example
3.15 to 3:15
Thanks for your participation.
Cheers
Ian
TIME(INT($A1),100*($A1-INT($A1)))
It converts numbers to time format
example
3.15 to 3:15
Thanks for your participation.
Cheers
Ian
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
EE1.xlsx