date and time formatting

**eturn** · 01-26-2011, 04:05 PM

Hello,

I'm trying to translate a date and time that comes in a format of MM DD and HOUR in three different columns, and with just four numbers for the time (ie 1000)

What I'd like to do is get all the information into a single cell that has the format

mm/dd hh:mm

I've tried using the RIGHT () and LEFT() commands to get the date information in, but it doesn't seem to allow me to format the cells where I've done the Right and Left commands. Is there someway I can get the information from these original cells, and still be able to format it correctly so when I plot, it will show the day and time?

thank you!

Erik

**TMS** · 01-26-2011, 04:17 PM

Can we see your formula? If you identify the cells where the date and time are held, we should be able to work it out.

Better still, post a sample workbook with examples of the values you are processing.

Regards

**TMS** · 01-26-2011, 04:24 PM

It would look something like this:

=DATE(YEAR(TODAY()),A2,B2)+TIME(LEFT(C2,2),RIGHT(C2,2),0)

formatted as: dd/mm/yyyy hh:mm

A2 contains the month, B2, contains the day and C2 contains the 4 digit time.

Regards

**eturn** · 01-26-2011, 06:04 PM

excellent! thanks much for the help!!

**DonkeyOte** · 01-26-2011, 06:06 PM

FWIW - on a US config. you might also try:

Please Login or Register  to view this content.

**eturn** · 01-26-2011, 06:41 PM

@TMSHucks

I've actually found that your solution doesn't work for rows where the hours is only 3 digits (ie before 10am). I added leading zeros with a custom format, but that didn't help.

thoughts?

thanks,
Erik

**DonkeyOte** · 01-26-2011, 06:50 PM

Originally Posted by eturn

I added leading zeros with a custom format, but that didn't help.

Altering the format of a cell does not affect the underlying value, eg:

930

format as 0000 will display as 0930 but the underlying value of 930 persists from a dependant calculation perspective

To resolve: you should find applying a Text format (as previously outlined) generates the requisite value.