Time issue: CONCATENATE or MERGE?

[alanj]

I have a time sheet that comes to me in an awkward format.


In Column B is the time, 8:00 and in Column C is AM or PM.

Excel will default the date in Column B as AM. I need it to match Column C.


When I do this: =B3&C3 - it gives me a crazy long number with AM or PM at the end. It can't be used as a time format.


Are there any alternatives?

 

[Nunes]

= B3 + if(C3 = "AM" , 0 , 0.5)

 

[alanj]

Nunes! You've done it! Nice work.

What is the logic behind it? (how does it work?)

 

[Nunes]

hey there :D

glad i could help.


the logic behind it...


well, basically, the main thing when working with dates and times in excel is realizing that the basic "unit" is the day, which means 1 is always 1 day...

this means 1 is a day, 7 is a week, 1/24 is an hour, 1/(24*60) is a minute and so on.


what i did in your specific case is very simple, actually, if you realize that 1 is one day and therefore 0.5 is half day, which equals 12 hours.

If you have 8:00 in a column and AM/PM in another, you should, in your "target" cell, have as a result 8:00AM or 8:00PM, but 8:00PM is 8:00AM plus 12 hours. you told us that in the first column excel was considering by default AM, so, if you want AM, your good with the 1st column... if you want PM, you just have to add 12 hours (aka 0.5 "excel time units" aka days)...


therefore the if


= B3 + if(C3 = "AM" , 0 , 0.5)

 

[alanj]

One thing, Nunes. It works for every value except 12:00pm and 00:00am. It always puts the opposite value. Any thoughts?

 

[fred]

0:00 AM = 12:00 AM they are identical to me. your condition of 12:00 + PM converting into 12:00 AM can be mitigated by adding another specific if/then condition.

 

[Luke M]

This will catch the noon hr oddity:

= B3 + IF(OR(C3 = "AM",B3>=0.5), 0, 0.5)


Using AM/PM notation, there should not be a 0:00 time slot...I'm assuming this should be displayed as:

12:00 AM

 

[alanj]

Clever!