Convert number to Date and Time

[Ri_star]

Hi All,
I have three cells.
In one cell there is Date, in 2nd cell there is time and in last cell there is AM/PM.
I wanted all of them in one call so I concatenate them but after that I am getting result as 45300 0.486006944444444 AM.
Can anyone please help me. How can I change it to Date and Time Format?

 

[Joe4]

Excel actually stores dates and times, specifically the number of days since 1/0/1900 (and time is just the fractional component of one day, i.e. 6:00 AM is equal to 0.25 since 6/24 is 0.25).
So, to combine date and time, you would just add up those two cells, and then apply a date/time format to the resulting cell.
That should work, as long as your entries are entered as real date/times, and not as strings.

For the AM/PM part, you may just need to add 12 hours (0.5), i.e.
if AM or PM is in cell A3, then the part of the formula would look something like:

... + IF(A3="PM",0.5,0)

 

[Ri_star]

Excel actually stores dates and times, specifically the number of days since 1/0/1900 (and time is just the fractional component of one day, i.e. 6:00 AM is equal to 0.25 since 6/24 is 0.25).
So, to combine date and time, you would just add up those two cells, and then apply a date/time format to the resulting cell.
That should work, as long as your entries are entered as real date/times, and not as strings.

For the AM/PM part, you may just need to add 12 hours (0.5), i.e.
if AM or PM is in cell A3, then the part of the formula would look something like:

... + IF(A3="PM",0.5,0)

Thank you so much for the quick help Joe4. It worked.

 

[Joe4]

You are welcome.
Glad I was able to help!