merging date and time

[twright]

I have an excel spreadsheet with date and time listed in adjacent
columns. The time is given as hr:min:sec with the seconds given a
decimal value, e.g., 8:15:35.53. I'd first like to eliminate the
seconds. Does excel have any use of wild card characters as in word so
I could search on colon/any
character/anycharacter/period/anycharacter/anycharacter? tried
reformatting, but excel doesn't seem to like the decimal seconds.

Then, for a plotting program I want to format the data as date and
time, i.e. m/d/yyyy hh:mm. I've tried using the & to combine cells, but
I can't get to a date and time that I can format in one cell.

If there is a solution to this I would like to hear about it. Tnanks in
advance for any information.

Sincerely--Tom Wright

 

[JE McGimpsey]

I have an excel spreadsheet with date and time listed in adjacent
columns. The time is given as hr:min:sec with the seconds given a
decimal value, e.g., 8:15:35.53. I'd first like to eliminate the
seconds. Does excel have any use of wild card characters as in word so
I could search on colon/any
character/anycharacter/period/anycharacter/anycharacter? tried
reformatting, but excel doesn't seem to like the decimal seconds.

Then, for a plotting program I want to format the data as date and
time, i.e. m/d/yyyy hh:mm. I've tried using the & to combine cells, but
I can't get to a date and time that I can format in one cell.

First, you need to know how XL handles dates and times. Dates are
integer offsets for the number of days since a base date, so in the Mac
default 1904 date system, 0 is displayed as 1 January 1904, and 37353 is
displayed as 8 April 2006.

Times are stored as fractional days, so 03:00:00 = 0.125.

Both date and time can be added, subtracted, multiplied and divided...

So one way to accomplish your goal would be

A1: 4/8/2006
B1: 8:15:25.53
C1: =A1+B1

which will return

4/8/2006 08:15

when C1 is formatted as "m/d/yyyy hh:mm". When formatted as General it's
37353.34405.

The only wild card here is what you mean by "eliminate the seconds". In
the formula above, the 25.53 seconds is still stored in the value. To
actually eliminate them, you'll have to round, round up or round down -
and you don't specify which you want. To simply round to the nearest
minute:

=ROUND((A1+B1)*1440, 0)/1440

(1440 is the number of minutes in a day: 24*60)

To round down:

=FLOOR(A1+B1,"00:01")

To round up:

=CEILING(A1+B1,"00:01")

 

[twright]

Thank you so much!! I was under the impression I had to use & rather
than + and I didn't know about the ceiling function.--Tom