Formula to show which scan is required based on gestational age?

Question

I currently have a formula to calculate gestational age which is gives me gestational age in 00w 0d format which works well and I have no issues with

However I would like to in a different column identify which scan would be required at the point of referral, for example if 13w 0d or less patient is for nuchal, if between 13w 1d and 18w 0d, than dating, from 18+0 to 22+0 than anomaly and anything above 22w 0d would be growth. I have tried many things but cannot get it to work. Is there a way or should I just give up?

Answer 1

Hello. Since your gestational age is already in a 00w 0d text format, the main task is converting that into total days so Excel can compare it to your scan thresholds.

I had a test with this formula:

=LET( txt, G12, w, VALUE(LEFT(txt, FIND("w", txt)-1)), d, VALUE(MID(txt, FIND(" ", txt)+1, FIND("d", txt)-FIND(" ", txt)-1)), totalDays, w*7 + d, IF(totalDays <= 91, "Nuchal", IF(totalDays <= 126, "Dating", IF(totalDays <= 154, "Anomaly", "Growth") ) ) )

You just need to paste the formula on the cell next to the Gest Age, and drag down. Let me know if it worked for you.