The unions had recommended its members back the deal which would see a 6.5% increase from April.
On top of a 7.5% pay rise imposed for 2022/23, it means many staff will see their pay increase by 13-14% over two years.
The votes to accept the offer ends the immediate threat of strike action.
It comes after members of GMB Scotland, the union representing NHS and ambulance staff, accepted the offer last week.
All three unions had mandates to strike after rejecting the 2022/23 pay offer but they were suspended in January ahead of intensive negotiations over the 2023/24 pay deal.
The new deal also includes the commitment to modernising the Agenda for Change pay system, and to support workforce recruitment, sustainability and retention.
The Scottish government has previously said the offer would make Scotland's NHS staff by far the best paid in the UK.
The RCN confirmed just over 50% of eligible members took part in the consultative ballot with 53.4% voting to accept the offer.
Among RCM members, 44% took part in the ballot, with 69% voting to accept the deal
Announcing the result of the ballot, RCN director Colin Poolman praised the union's members for bringing the Scottish government back to the table. He added: "Members have narrowly voted to accept this offer but the Scottish government must be under no illusion, much more is required for nursing staff to feel valued and to ensure Scotland has the nursing workforce it needs."
Julie Lamberth, chair of the union's Scotland board said it took "the real threat" of nursing strikes to secure the offer.
She added: "While members voted by a narrow margin to accept the offer, the chronic staff shortages and low morale that led to the strike mandate are still very real."
Jaki Lambert, RCM director for Scotland, added: "While pay is crucial this was also about midwives feeling seen and valued. Improving retention through better working conditions, professional midwifery issues and the wellbeing of staff are also a key component of this.
"Most importantly, it was also about our members standing tall and being prepared to take action to ensure better care for women, babies, and their families."
The Scottish government said it had committed an extra £568m to the 2023/24 offer to 160,000 NHS Scotland workers on Agenda for Change contracts - who includes nurses, paramedics, midwives and porters.
Staff up to Band 8a would see an uplift of at least 6.5%.
In addition, all staff would receive a one-off payment between £387 and £939 depending on banding.
Meanwhile, in England a 5% pay rise from April has been offered to NHS staff including nurses and ambulance workers.
In addition, staff have been offered a one-off payment of at least £1,655 to top up the past year's pay award.
Unions are recommending members back the deal, after nearly two weeks of talks with ministers, raising hopes the bitter dispute may be coming to an end.
The offer covers all NHS staff except doctors, who are on a different contract.
It comes after a winter of industrial action, with nurses, ambulance staff and physios all striking.
The unions put further action on hold, after the two sides agreed to discussions last month.