My thought was that if the OS is on an SSD, the HDDs will spin down at night when they are not being accessed.
I tried this some years ago with Windows XP and IBM/Hitachi drives, which have progressive power saving features (first timer unloads the heads, second slows down the platters and the final puts the drive to sleep - or something like this). Unfortunately the results where disappointing. Windows likes to "ping" the drives from time to time. Check the windows shared folders? Spins up the drives. Temperature monitoring software runs? Spins up the drives. Some unknown scheduled task runs? Spins up the drives.
You get the picture. Although some of these things might have changed with the newer windows versions.
What I finally did was move everything to my firewall box, which runs OpenBSD. I had some tweaking to do, exclude the storage drive paths from some daily/weekly/monthly script checks (file security checks, whatis database builder, free space checker, etc ) but in the end it did what I wanted. The drives remained in sleep until I wanted to access a file off one of them. But I'm currently not using this option, my NAS is in another room =)
Is there some way of having the fans switch off when the temperature is low enough with the HDDs spun down?
You could setup a custom, M/B temperature based profile with SpeedFan or such but you would have to instruct SpeedFan not to check the HDD temperature and to completely ignore them (no SMART checks, no nothing) as that would most probably cause the drives to spin up. I'm not sure if that's possible with it though.