Is it okay to fall asleep during night prayers or is it sinful?
In my opinion what you should consider more is habit. Why did you or are you falling asleep during night prayers? If you do not do this on purpose; do not position yourself for the sleep and anticipate it; if you try to fight it off when it comes then it is not a sin. It is not okay, but not a sin. However, if you are lax in prayer, are looking forward to sleep (rarely the case for many), and are ALWAYS willing to blow off your chance to communicate with God for a few minutes at night, then this could be at least an imperfection or a venial sin. It is very unlikely that a practicing catholic wouldn’t be able to make out at least five minutes at night to say a quick “hello” to our Heavenly Father.
We need to look at God as a Father. Prayer is a dialogue with a father who loves and forgives our little weaknesses. So we needn’t beat ourselves up so much. If you work so hard all day, come back to screaming kids and have a ton of chores to complete before night prayers and bed, then I am sure God will understand. A little caveat though: DO NOT LET THIS BECOME HABITUAL. Habitually falling asleep during night prayers is definitely not a good thing. Occasionally doing this might be understandable but not habitually.
Consider changing your time of prayer. Once you return from work, from 6 or 7pm before you become weakened by sleep, try and complete your night prayers. I like to do this so that when I finally lie on my bed I can sleep off with Hail Mary on my lips. We must watch out, we are in constant battle with the Enemy. He could base his temptations on natural human weaknesses and make us go overboard with laziness.
Whilst it is not necessarily sinful to fall asleep during prayers, it is not good and shouldn’t be encouraged in our lives. We must do all we can to get a clear-head-time with God before sleep comes. However we needn’t feel too guilty about momentary lapses rising from extraordinary occasions.