Ahora que

Ahora que has present meaning when the clause is rendered in present indicative: Ahora que lo pienso, sí habla un poco chistoso. Now that I think about it, he does talk a little funny. No lo había pensado hasta ahora que lo dices. I hadn't thought of it that way, but now that you mention it, it does make sense.

Similarly, when rendered in the past, the meaning is past: A ver qué pasa con él ahora que regresó su novia. Let's see how he reacts now that his girlfriend is back.

Ahora que regrese

Things get interesting when the clause is rendered in subjunctive. Here the meaning is future.

• Ahorita que venga. When he gets here (in a bit). As soon as he gets here.
• Ahora que regrese. When you (usted) get back. As soon as you get back.
• Ahorita que me bañe. As soon as I'm done showering.
• Ahorita que pare la lluvia. When the rain stops. As soon as the rain stops. Once the rain has stopped. (It won't be long now.)
• Ahorita que me anime a salir. As soon as I get my energy up to go out.
• Iba a hacer algo ahorita que subiera. I was going to do something when I got upstairs (but now that I'm upstairs, I don't remember what it was). Here we have past subjunctive since the meaning is future in the past.

Say you're heading out and someone gives you a task to do. Maybe the roommate wants you to wash the dishes. Or the doorman needs you to read a document and sign it. How could you postpone fulfilling the request? — Ahorita que regrese. I'll do it as soon as I get back.

Note that in these examples with future meaning, we're focused on the change of state. Now it is raining. In a moment it will stop raining. We're interested in the situation that begins after that moment. Ahorita que pare la lluvia. That's why as soon as is a good translation; it focuses on the moment of transition.

Yes, you could express these ideas with cuando or en cuanto, but try ahora/ahorita que the next time it fits. And keep your ears open. You'll notice it's more common than you may have realized.

Ayer que, mañana que

This construction can work with other adverbs of time, whether past or future.

• Ayer que lo vi. When I saw him yesterday.
• Te marco mañana que llegue. I'll call you tomorrow when I get there.

Don't confuse these with constructions such as la semana que viene, which simply means next week, not when next week gets here.