Adding two hours to a format that ISN'T classified as a time.

[timtim]

Hi there,

I need to find a way, preferably a quick way ( :-) ), to add TWO hours to a number that isn't classified by Excel as a 'time' format (it is in format 'hh:mm:ss:00 AM/PM', so when trying to add two hours it won't - and I need it in this EXACT format for when I paste it to another macro), and then also changing one of the other cells to have a '2' next to it (for instance, change 'ARENA' to 'ARENA2').

An example of one of the rows of data:

Please Login or Register  to view this content.

Ok, so it is the '06:18:54:00AM' that I need to add two hours to (to signify a '+2' TV channel basically). I have used a VLOOKUP to change the channel 'ARENA' to 'ARENA2' (because not all channels have a +2, so it won't be all rows this is needed on) but am stumped on adding two hours without doing a ton of work (there is sometimes up to 1000+ rows in this). Also when I do the VLOOKUP it obviously means I get a %VALUES when it hits a channel that does not have a +2 channel.

My whole task in a nutshell: Getting a huge list of rows with that information on, duplicating it and adding two hours and changing the channel for the ones with a +2 channel, and then duplicating it and changing the channel (but not the time) for those with a similcast HD channel. It's all very confusing and I wish there was an easy way to do it!

Thanks, and if there's any questions let me know.

Or if you need any extra information.

EDIT: I'VE ATTACHED SOME MORE INFORMATION OF WHAT I NEED IN AN EXCEL FILE, WITH TWO TABS. thanks!

[6StringJazzer]

It's certainly possible to write a formula (or a macro) with logic to add two hours and then take care of what will happen if it causes a date rollover, but the reason that Excel has a date format is to provide that logic to you reliably and transparently.

From a total-solution standpoint I would advise fixing the problem at its root cause. Why are you tied to this format?

Edit: Oh, and what do the last two digits mean? I've never seen a time format (in Excel or anywhere else) with three colons.

[timtim]

Hi there,

Sorry for the delay on the response.

The reason it needs to be in this format is because there's ANOTHER Macro (that someone else made, who doesn't work here any more) that then transforms the format you see in the attachment (stuff in grey) to another format that can be read by this TV ratings analysis software we use. So the Macro needs the time format like that... (I've checked to see what the Macro spits out with the date format adjusted to a 'time' format and it comes out wrong)

Thanks.

[timtim]

Oh, and the last two digits are milliseconds I think - again they always tend to be '00' so they're not needed EXCEPT for the fact the other macro needs it in that exact format...

[6StringJazzer]

This shows you how to add two hours, take that into account in the date, and produce results that match your original formats.

I did not include a test for whether it's in the list of channels to convert; you already have a lookup for that and seem to have a handle on it. So there is a little more work for you to do.

BTW those last two digits won't be milliseconds because there are only two digits. Normally a format with milliseconds looks like

08:42:13.169 AM

[timtim]

Hi mate,

That's brilliant - so after doing that formula you simply copy it into the columns and it's got the +2 channels?!

Only one question - I 'think' the AM and PM is mixed up sometimes with it, but I can't see why from the formula.

For instance, F10 says 10:46:23:00PM but N10 says '6/20/2011 10:46:23 AM' - so the AM and PM seems to have switched? This has happened on a few of them.

Apart from that, it's nearly perfect!

Thanks for the help.

[timtim]

And agreed about the milliseconds - tbh, I'm not completely sure. The results are from one system, and all of this is so another system can read it (with added HD and +2 channels).

Thanks again.

[6StringJazzer]

Oops, bug. Fixed it.

I also added columns R, S, T which have your final results. Those results depend on the intermediate "Helper" columns I did the first time. The formulas could be collapsed into one formula for the date and one for the time if need be.

[timtim]

Hi mate,

Thanks for the fix, I've used those and made the perfect template to add +2 and HD channels.

I actually use the #N/A error to tell me the channels that don't have a HD or +2, so the process is really easy now (I've made it do it like a machine).

1. Paste data into grey area.
2. Sort by Channel.
3. Scroll right to what looks like an exact replica of the gret area, but it has the adjusted dates, times and channels (by simply doing an '=cell' in all of it).
4. Copy and 'paste values' all of that into a new tab, sort it AGAIN by channel (so all the #N/A is at the bottom).
5. Copy all data up until the last available channel (all up to the first #N/A).
6. Paste into the original Macro file I have.

This is how I do it for the +2, and it's basically the same for the HD (but it only needs a look-up table, no crazy complicated formula for the date and time!).

So thanks a ton for the help, if you live in Australia I owe you a pint!